Excel Questions

Started by magickingdom, February 21, 2008, 12:29:11 PM

Previous topic - Next topic

T Fearon

Surely it's simple to construct your own spreadsheet to do that.Asset description,value,rate of monthly depreciation with formula to calculate?

muppet

Quote from: T Fearon on September 27, 2013, 07:05:16 AM
Surely it's simple to construct your own spreadsheet to do that.Asset description,value,rate of monthly depreciation with formula to calculate?

Yes you tell him Tony.

Why would anyone come on here and ask how to do something, when TF already knows how to do it?
MWWSI 2017

Hardy

Try this.

Put some values in four columns. In the fifth column, sum the first three columns.

A1=1
B1=1
C1=1
D1=5
E1=Sum(A1:C1) = 3

Now delete the value in D1 and replace it with some other value.
See what happens to E1?

This happens in Excel 2007. I don't know about other versions. It cost my colleague two days of debugging in some VBA code he was doing this week.

armaghniac

Quote from: Hardy on May 24, 2014, 01:00:55 PM
Try this.

Put some values in four columns. In the fifth column, sum the first three columns.

A1=1
B1=1
C1=1
D1=5
E1=Sum(A1:C1) = 3

Now delete the value in D1 and replace it with some other value.
See what happens to E1?

This happens in Excel 2007. I don't know about other versions. It cost my colleague two days of debugging in some VBA code he was doing this week.

This doesn't present a problem for me in Excel 2010.
If at first you don't succeed, then goto Plan B

Hardy


armaghniac

Actually Hardy I read your instructions again, I was changing the value in D1 not deleting it. When I delete D1 and enter a new value I do get a problem! Libreoffice does not behave like this. That's a bit odd.
If at first you don't succeed, then goto Plan B

Hardy

Yeah - it's the usual stuff of systems trying to be too smart. When you delete the value in the column not included in the sum it resets its assumptions and decides you'd want to sum all columns in the range up to the result column.

Maiden1

Change the code in E1 to =SUM($A$1:$A$3) .  You can change other cells and it won't change the range.
There are no proofs, only opinions.

armaghniac

#278
Quote from: Maiden1 on May 24, 2014, 02:57:18 PM
Change the code in E1 to =SUM($A$1:$A$3) .  You can change other cells and it won't change the range.

you can do this, but in a spreadsheet you would probably have wanted to copy the formula.
Using the "everything up to current row/column" is a reasonable default for an initial entry for SUM, you can always change it.
But existing formulas changing because of the alteration of cells not in the range covered by the formula is not on.
If at first you don't succeed, then goto Plan B

Maiden1

Quote from: armaghniac on May 24, 2014, 03:04:46 PM
Quote from: Maiden1 on May 24, 2014, 02:57:18 PM
Change the code in E1 to =SUM($A$1:$A$3) .  You can change other cells and it won't change the range.

you can do this, but in a spreadsheet you would probably have wanted to copy the formula.
Using the "everything up to current row/column" is a reasonable default for SUM, you can always change it.
But formulas changing because of the alteration of cells not in the range covered by the formula is not on.

Definitely strange it does that.  Another way to do the formula is below where it offsets by 2 cells.  You can copy the formula using this.

=SUM(A1:OFFSET(E1,0,-2))
There are no proofs, only opinions.

Billys Boots

No version of excel likes you to do calculations in cells not adjacent to the cell involved, for some reason.  Not entirely sure why - actually I don't have the first idea. 
My hands are stained with thistle milk ...

Hardy

In Excel 2007, something weird happens when I try to do a Find and Replace in a range of COUNTIF formulae. Can anyone try the setup below and see if it happens and let me know the Excel version you're using? Also, any insight would be appreciated.



Column C is counting the number of instances of each category in the range A1 to A5. Now I want to count the number of instances of each category in the range A6 to A10. So I do a Find and Replace on the formula range C1 to C3. I put $1 in the Find box and $6 in the Replace box and hit Replace All.

Tell me what happens in your case.

take_yer_points

Quote from: Hardy on September 24, 2014, 11:28:32 AM
In Excel 2007, something weird happens when I try to do a Find and Replace in a range of COUNTIF formulae. Can anyone try the setup below and see if it happens and let me know the Excel version you're using? Also, any insight would be appreciated.



Column C is counting the number of instances of each category in the range A1 to A5. Now I want to count the number of instances of each category in the range A6 to A10. So I do a Find and Replace on the formula range C1 to C3. I put $1 in the Find box and $6 in the Replace box and hit Replace All.

Tell me what happens in your case.

Replaced $1 with $6 and it updated to the following - also using Excel 2007.

=COUNTIF(A$5:A$6,...

Tried:

Find: A$1:A$5
Replace with: A$6:A$10

That worked ok - not sure why the initial find and replace does that

Hardy

That's what I'm getting too. I can't understand what's happening in the first case. Are you using 2007 version too?

DuffleKing


BAsic one here - how do you input numbers to a cell (e.g. phone nbrs) without losing the 0 at the beginning?