MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Looking for column with data


Posted by Pat D on November 21, 2001 8:27 AM

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

I have 3 sheets
Each sheet is set up as follows
A..............B..........C......D.....E......Z...
FIRST NAME...LAST NAME...DAY1...DAY2...DAY3..BONUS
XXXXXXXXXX...YYYYYYYYY
AAAAAAAAAA...BBBBBBBBB


Column Z has a formula =X * 2

This calculates Bonus as double the value of the number in the cell where there is a non-zero value

So if the person AAAAAA last made a sale on DAY 4 (i.e. column F), the formula for that person's bonus in column Z would be

=F3*2

I have already sorted my sheets so that the people who last sold on Day 4 all appear on one sheet... people who last sold on Day 8 are on one sheet etc.

So I just need to have a formula that knows which column to use.

Sorry if I didn't make myself clear.



Posted by giacomo on November 21, 2001 8:55 AM

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


Posted by giacomo on November 21, 2001 8:57 AM

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.

Posted by Aladin Akyurek on November 21, 2001 9:07 AM

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


Posted by Mark W. on November 21, 2001 9:10 AM

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


Posted by Mark W. on November 21, 2001 9:19 AM

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


Posted by Pat D on November 21, 2001 9:27 AM

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


Posted by Pat D on November 21, 2001 9:28 AM

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

Posted by Aladin Akyurek on November 21, 2001 9:42 AM

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

Posted by Juan Pablo on November 21, 2001 9:49 AM

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

Posted by pat D on November 21, 2001 9:57 AM

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

Posted by Aladin Akyurek on November 21, 2001 10:08 AM

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 :

Posted by Aladin Akyurek on November 21, 2001 10:11 AM

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 :

Posted by Aladin Akyurek on November 21, 2001 10:57 AM

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

Posted by pAT d on November 21, 2001 10:59 AM

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 --

Posted by Aladin Akyurek on November 21, 2001 11:10 AM

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

Posted by Aladin Akyurek on November 21, 2001 11:19 AM

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,

Posted by Pat D on November 21, 2001 12:20 PM

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