Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
hello all, i have a row of 18 numbers that update autmatically and give me a
number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
Adapt this
=LOOKUP(2,1/((BA3:BR3<"")*(BA3:BR3<0)),BA3:BR3) if there can be no empty cells within you can use =LOOKUP(2,1/(BA3:BR3<0),BA3:BR3) -- Regards, Peo Sjoblom "Joe" wrote in message ... hello all, i have a row of 18 numbers that update autmatically and give me a number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
Try this array formula (assuming row 1. Change as needed):
=INDEX(A1:R1,1,MAX(--(A1:R1<0)*COLUMN(A1:R1))) For an array formula, instead of Enter, you have to press Ctrl + Shift + Enter. If you do it right, Excel will add curly braces around the formula. Hope this helps, Hutch "Joe" wrote: hello all, i have a row of 18 numbers that update autmatically and give me a number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
Try this:
=LOOKUP(2,1/BA3:BR3,BA3:BR3) -- Biff Microsoft Excel MVP "Joe" wrote in message ... hello all, i have a row of 18 numbers that update autmatically and give me a number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
Doesn't work if the last value is < 1 but 0
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... Try this: =LOOKUP(2,1/BA3:BR3,BA3:BR3) -- Biff Microsoft Excel MVP "Joe" wrote in message ... hello all, i have a row of 18 numbers that update autmatically and give me a number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
This should take care of that:
=LOOKUP(1E100,1/BA3:BR3,BA3:BR3) -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... Doesn't work if the last value is < 1 but 0 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... Try this: =LOOKUP(2,1/BA3:BR3,BA3:BR3) -- Biff Microsoft Excel MVP "Joe" wrote in message ... hello all, i have a row of 18 numbers that update autmatically and give me a number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
thank you all for your help, the first one i tried and its good. i have been
trying to figure that out for a few months now and was getting nowhere. keep up the good work. Joe "T. Valko" wrote: This should take care of that: =LOOKUP(1E100,1/BA3:BR3,BA3:BR3) -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... Doesn't work if the last value is < 1 but 0 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... Try this: =LOOKUP(2,1/BA3:BR3,BA3:BR3) -- Biff Microsoft Excel MVP "Joe" wrote in message ... hello all, i have a row of 18 numbers that update autmatically and give me a number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
On Thu, 1 May 2008 19:04:32 +0100, "Bob Phillips"
wrote: Doesn't work if the last value is < 1 but 0 While your statement is true, I don't see how that would occur given the OP's description. --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding last number in row excluding 0's
joe test
"Joe" wrote: hello all, i have a row of 18 numbers that update autmatically and give me a number increasing by 1 if the statement is true, if false, i get a 0. i would like to know of a formula that will give me the last number in the string excluding the 0's. example is columns BA3:BR3 and in column BS3 i would like to see last number over 0. the below example would be after 3 different days of inputs, as you can see it does not take the 0 but the last whole digit in the string. your help would be greatly appreciated thank you ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 0 0 =2 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 0 =3 ba bb bc bd be bf bg bg bi bj bk bl bm bn bo bp bq br bs 0 1 2 3 4 5 6 7 8 0 1 2 3 0 1 2 3 4 =4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding minimum value excluding zero | Excel Discussion (Misc queries) | |||
Finding Minimum Value in series, excluding zero values | Excel Worksheet Functions | |||
Finding Min Cell values excluding zero in alternate columns | Excel Worksheet Functions | |||
Finding Maximum value while excluding some values | Excel Worksheet Functions | |||
Finding Min Cell values excluding zero in alternate columns | Excel Worksheet Functions |