Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for finding a cell with Anumber in it
Hello again
Is there a function that will look up a column and then use the next cell it finds with a number greater than 0, so if I have a column b1 through b31 with say 100 in b1 then I want to subtract tomorrows number in b2 say 200 from b1 then subtract b3 from b4 and so on. but sometimes they forget to take the readings for the day and I have to put in a zero for the cell, then when they do take there next reading I have to put the number in for that day and I get a minus and the number I just entered. what they bring me is a log sheet that they use to record the nimbers. hope this makes sense. -- Thank You Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for finding a cell with Anumber in it
Hi Steve
the following array formula will give you the last value in the range that is non Zero {=LOOKUP(99^99,IF(B1:B31<0,B1:B31,""))} Array formulae must be entered or edited using Control+Shift+Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not type them yourself. In your case, you want the range to be increasing as you move down so the formula entered in say cell C2, would be {=B2-LOOKUP(99^99,IF($B$1:$B1<0,$B$1:$B1,""))} Copy down through C3:C31 -- Regards Roger Govier "steve" wrote in message ... Hello again Is there a function that will look up a column and then use the next cell it finds with a number greater than 0, so if I have a column b1 through b31 with say 100 in b1 then I want to subtract tomorrows number in b2 say 200 from b1 then subtract b3 from b4 and so on. but sometimes they forget to take the readings for the day and I have to put in a zero for the cell, then when they do take there next reading I have to put the number in for that day and I get a minus and the number I just entered. what they bring me is a log sheet that they use to record the nimbers. hope this makes sense. -- Thank You Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for finding a cell with Anumber in it
Thank you I'll try and work with it as soon as I can, you are great helping
us out with this. -- Thank You Steve "Roger Govier" wrote: Hi Steve the following array formula will give you the last value in the range that is non Zero {=LOOKUP(99^99,IF(B1:B31<0,B1:B31,""))} Array formulae must be entered or edited using Control+Shift+Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not type them yourself. In your case, you want the range to be increasing as you move down so the formula entered in say cell C2, would be {=B2-LOOKUP(99^99,IF($B$1:$B1<0,$B$1:$B1,""))} Copy down through C3:C31 -- Regards Roger Govier "steve" wrote in message ... Hello again Is there a function that will look up a column and then use the next cell it finds with a number greater than 0, so if I have a column b1 through b31 with say 100 in b1 then I want to subtract tomorrows number in b2 say 200 from b1 then subtract b3 from b4 and so on. but sometimes they forget to take the readings for the day and I have to put in a zero for the cell, then when they do take there next reading I have to put the number in for that day and I get a minus and the number I just entered. what they bring me is a log sheet that they use to record the nimbers. hope this makes sense. -- Thank You Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for finding a cell with Anumber in it
In C2: =IF(B20,B2-B1,"")
copy down as far as needed "steve" wrote: Hello again Is there a function that will look up a column and then use the next cell it finds with a number greater than 0, so if I have a column b1 through b31 with say 100 in b1 then I want to subtract tomorrows number in b2 say 200 from b1 then subtract b3 from b4 and so on. but sometimes they forget to take the readings for the day and I have to put in a zero for the cell, then when they do take there next reading I have to put the number in for that day and I get a minus and the number I just entered. what they bring me is a log sheet that they use to record the nimbers. hope this makes sense. -- Thank You Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding cell that returns bad "solver_val" function. | Excel Discussion (Misc queries) | |||
Using a formula on anumber of cells in a column that the user inpu | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
function for finding a value! | Excel Worksheet Functions | |||
Help: I need a function for finding next cell vertically with value | Excel Worksheet Functions |