Excel Help Please

Colbaker

Colbaker

Colbaker

Associate
Joined
8 Dec 2004
Posts
1,970
Location
Paignton, Devon
Calling all those Excel guru's out there...

I am trying to find an equation that will put the final number in a column into the cell, for instance i have 30 rows in column A and i want the equation in Cell A31 to give me what the last number entered in the 30 rows, Now the issue is that not all 30 rows can be populated i may on have 20 items in the 30 rows so will need to know what the 20th number is,

Hope ive explained it ok

Thanks
 
Colbaker said:
any idea what this means?

=SUM(L6)/SUM($F$5:F6)

what does the $ mean?
Click to expand...

In all honesty you need to find some basic excel tutorials if you are finding out about this sort of stuff. "$" means that if the formula is copied to another cell the referenced row/col wont change.
 
Thanks for the reply's guys, I could not understand that link hodders sorry, problem is i stupidly told my boss i would do this thinking that it would be easy (man was i wrong).

If i explain it better with a piccy, i need to fill in the orange cell with the last number in the column and if i add to that column it will automatically change the amount in the orange cell.

Thanks for the reply's guys, I could not understand that link hodders sorry, problem is i stupidly told my boss i would do this thinking that it would be easy (man was i wrong).

If i explain it better with a piccy, i need to fill in the orange cell with the last number in the column and if i add to that column it will automatically change the amount in the orange cell.

http://oneblondeeyebrow.co.uk/displayimage.php?album=random&cat=0&pos=-450

thanks.
thanks.
 
since the numbers are getting bigger as you go down it would be easiest to just use =max(a4:a9999)

I'm assuming that you are in column A, obviously change that as needed
 
I thought the numbers were getting bigger as well but they aren't there are a few higher numbers in there.

Any other idea's?
 
try this =INDIRECT("A"&(3+COUNTA(A4:A9999)))

it counts how many non-zero values there are then returns the value in the last cell (the +3 is needed to adjust for the fact your values start from row 4 not row 1

again, edit this if you arent in column A
 
O.k. i tried that as i am in column M so changed accordingly but it now comes up with #N/A would this be because it is currency? the output should be 25.49 as this is the amount in the bottom cell of the column, it would also seem as if the #N/A at the bottom of the column is causing issue's but i need to keep them in as they have another equation in them.

Ricky1981, you have a point there, Surely CUMULATIVE means the numbers should be going up?
 
Last edited:
That's probably because you have entered a value in the "empty" cells rather than leaving them blank, replace counta with count and it should work
 
All done.

Thanks for your help guys, I can now pass this off as my own work and get some kudos from the boss :D

(hopefully he then does not make me the chief excel spreadsheet creator)
 
ricky1981 said:
I would add that any cumulative total that doesn't keep going up should be a cause for concern! ;)
Click to expand...

Colbaker said:
Ricky1981, you have a point there, Surely CUMULATIVE means the numbers should be going up?
Click to expand...

Not really as cumulative sums all numbers, positive or negative.

So you might have

1
2
-3
4

etc., and the cumulative sum after the 2 will be elss than it was before it.
 
Robbie G said:
Not really as cumulative sums all numbers, positive or negative.

So you might have

1
2
-3
4

etc., and the cumulative sum after the 2 will be elss than it was before it.
Click to expand...

I didn't mean the maths was wrong, just that given the numbers were currency and presumably some form of sales statistics it would be worrying if the total was dropping as it would indicate negative amounts! ;)
 
You must log in or register to reply here.
Back
Top Bottom