Looking for column with data

=INDEX(A1:Y1,,COUNTA(A1:Y1))*2 (nt)

one condition...

Sorry - I forgot to mention this only works if all the cells before your last date have values in them. It sounds like that's the case from your description.

I have the impression that you need

=IF(ISNUMBER(MATCH(9.9999999999999E+306,C2:X2)),INDEX(C2:X2,MATCH(9.9999999999999E+306,C2:X2))*2,"")

to be entered in Z2 and copied down.

Aladin

===== I want a formula to use the data from the last column that has data in it

=MATCH(9.99999999999999E+307,$C$1:$Y$1)*2 (nt)

Make that... =MATCH(9.99999999999999E+307,$C1:$Y1)*2 (nt)

Thanks for trying to help but it hasn't worked so far... my spreadsheet is actually a little more complicated than I posted but I thought if I simplified it I'd be able to get the same results. What actually is the case is the sheet is set up like this:

First Name...Last Name... Day1Price...Day1Volume...Day1Sale

so for each Day there are three numbers, the price, the number of units and the value of the sale.

So it needs to look at the last column where there is a Sales number... my sheet is setup so that if, let's say Day 3 is the last time a sale was made, Under the Day4Price, Day4Volume and Day4Sale column there is a zero.. and so on until the end of the month.

So if Day1Sale is in column F, Day2Sale is three columns over i.e. in column I. So i need the formula to only look at the columns that contain DaySale values.

Thanks

First Name...Last Name... Day1Price...Day1Volume...Day1Sale so for each Day there are three numbers, the price, the number of units and the value of the sale. So it needs to look at the last column where there is a Sales number... my sheet is setup so that if, let's say Day 3 is the last time a sale was made, Under the Day4Price, Day4Volume and Day4Sale column there is a zero.. and so on until the end of the month. So if Day1Sale is in column F, Day2Sale is three columns over i.e. in column I. So i need the formula to only look at the columns that contain DaySale values. Thanks

Pat --

If the last value entered in a row is DayNSale, then e.g., the formula that I gave should work. Are you trying to say this is not true?

Aladin

: I want a formula to use the data from the last column that has data in it

Aladin, if the last "real" number, sort of speak, is 15, but there are 0's under that (As i understood, if Day 3 has the last sale there's a number [16], and Day 4, 5 and so on will have 0's), then MATCH(9.999E+307,C2:X2) will return the position of the last 0, not the 16... i guess that's the error.

Juan Pablo Pat -- If the last value entered in a row is DayNSale, then e.g., the formula that I gave should work. Are you trying to say this is not true? Aladin : Thanks for trying to help but it hasn't worked so far... my spreadsheet is actually a little more complicated than I posted but I thought if I simplified it I'd be able to get the same results. What actually is the case is the sheet is set up like this

Aladin,

Yeah actually i reversed the order when I posted the message.. it's actually DayNSale and then it provides the breakdown by price and volume.
SO column A = Sale, B = Price, C = volume and I want formula to refer to the Sale column

So Just for simplicity, If Day 1 has an entry in all 3 columns, and Day 2 has an entry in all 3 columns, but there were no sales on Day 3, then there would be a zero or #DIV/0 entry (more specifically, under the Sales column it would say Zero, Under the price it would be 0 and under Volume would be #Div/0 because it is calculated by dividing sales by price). So in the last column I want it to calculate bonus based on the sales figure in Day 2.

Regards

If Juan is right, you need to use an array-formula

=IF(MAX(C2:X2)>0,INDIRECT(ADDRESS(ROW(C2:X2),MAX((C2:X2>0)*(COLUMN(C2:X2)))))*2,"")

You need to hit control+shift+enter in order array-enter a formula, not just enter.

However, I'd suggest leaving empty the cells instead of putting 0's, so that you can use a more efficient formula such as one that I suggested in my first reply. : Pat -- : If the last value entered in a row is DayNSale, then e.g., the formula that I gave should work. Are you trying to say this is not true? : Aladin :

That's better...

Yeah actually i reversed the order when I posted the message.. it's actually DayNSale and then it provides the breakdown by price and volume.

Use:

=IF(ISNUMBER(MATCH(9.9999999999999E+306,C2:X2)),INDEX(C2:X2,MATCH(9.9999999999999E+306,C2:X2)-2)*2,"")

Aladin

============= So Just for simplicity, If Day 1 has an entry in all 3 columns, and Day 2 has an entry in all 3 columns, but there were no sales on Day 3, then there would be a zero or #DIV/0 entry (more specifically, under the Sales column it would say Zero, Under the price it would be 0 and under Volume would be #Div/0 because it is calculated by dividing sales by price). So in the last column I want it to calculate bonus based on the sales figure in Day 2. Regards Pat -- : If the last value entered in a row is DayNSale, then e.g., the formula that I gave should work. Are you trying to say this is not true? : Aladin :

By the way...

=IF(ISNUMBER(MATCH(9.9999999999999E+306,C2:X2)),INDEX(C2:X2,MATCH(9.9999999999999E+306,C2:X2)-2)*2,"") : Yeah actually i reversed the order when I posted the message.. it's actually DayNSale and then it provides the breakdown by price and volume. SO column A = Sale, B = Price, C = volume and I want formula to refer to the Sale column : So Just for simplicity, If Day 1 has an entry in all 3 columns, and Day 2 has an entry in all 3 columns, but there were no sales on Day 3, then there would be a zero or #DIV/0 entry (more specifically, under the Sales column it would say Zero, Under the price it would be 0 and under Volume would be #Div/0 because it is calculated by dividing sales by price). So in the last column I want it to calculate bonus based on the sales figure in Day 2.

I didn't read the second paragraph. In order to use the above formula, you need to make the following changes in your worksheet.

If there is no sales, leave the corresponding cell empty (don't put a 0 there). Leave the price cell also empty. Finally, modify the =Sales/Price formula as follows:

=IF(Sales,Sales/Price,"")

The above formula will then work as intended.

Aladin

Re: That's better... - Still not working?

I entered in the formula below (changing the column and row values to match my spreadsheet), and it is always returning a 0.

Should the formula below return the following results:

D1S D1P D1V D2S D2P D2V D3S D3P D3V...BONUS
100 10 10 200 20 10 0 0 0 400
500 50 10 400 10 40 0 0 0 800

And if the data was:

D1S D1P D1V D2S D2P D2V D3S D3P D3V...BONUS
100 10 10 0 0 0 0 0 0 200
500 50 10 0 0 0 0 0 0 1000

Also, let's say the last 3 columns next to the Bonus calculator have numbers in them:

....D30S D30P D30V BONUS
....100..10....10...200

Thanks

Aladin, : Yeah actually i reversed the order when I posted the message.. it's actually DayNSale and then it provides the breakdown by price and volume. SO column A = Sale, B = Price, C = volume and I want formula to refer to the Sale column Use: =IF(ISNUMBER(MATCH(9.9999999999999E+306,C2:X2)),INDEX(C2:X2,MATCH(9.9999999999999E+306,C2:X2)-2)*2,"") Aladin ============= : So Just for simplicity, If Day 1 has an entry in all 3 columns, and Day 2 has an entry in all 3 columns, but there were no sales on Day 3, then there would be a zero or #DIV/0 entry (more specifically, under the Sales column it would say Zero, Under the price it would be 0 and under Volume would be #Div/0 because it is calculated by dividing sales by price). So in the last column I want it to calculate bonus based on the sales figure in Day 2. : Regards : : Pat --

Juan...

the array-formula that I suggested needs correction:

=IF(MAX(C2:X2)>0,INDIRECT(ADDRESS(ROW(C2:X2),MAX((C2:X2>0)*(COLUMN(C2:X2)))))*2,"")

It must be just:

=INDIRECT(ADDRESS(ROW(C2:X2),MAX((C2:X2>0)*(COLUMN(C2:X2)))))*2

With the IF-part included, the formula will not work, due I think to the semantics of INDIRECT.

Note. If the range C2:X2 is either blank or contains formulas that return blanks, it will also not work, but that's normal.

Aladin

It will if...

you can implement changes to your worksheet that I propose at

(the "That's Better" post)

Cheers.

Aladin

======== I entered in the formula below (changing the column and row values to match my spreadsheet), and it is always returning a 0. Should the formula below return the following results: D1S D1P D1V D2S D2P D2V D3S D3P D3V...BONUS

: Aladin,

Re: It will if...

Thanks alot!
Would never have figured this out you can implement changes to your worksheet that I propose at the "That's Better" post Cheers. Aladin ======== : I entered in the formula below (changing the column and row values to match my spreadsheet), and it is always returning a 0. : Should the formula below return the following results