How to input multi if

[josaiah]

Hi, how can i nest up all the IF?

a b c d e f
1 19/7 18/7 17/7 16/7 15/7 14/7
2 Closed 82.06 82.42 83.00 81.00 85.00

I want my cell to pick up the value at roll 2 with the latest date. but if my cell is value "Closed" pick up the previous date with a value in it.

and if my cell a2 and b2 is closed pick up c2 and so on.

pls advise.

 

[Andrew Fergus]

Hi

Assuming the highest dates are on the left, the following array* formula should do the trick:

=INDEX(2:2,1,MIN(IF(ISNUMBER(2:2),COLUMN(1:1),9^9)))

This assumes row 1 contains the dates and row 2 contains the values. This is seeking the first column in row 2 where there is a value. It isn't actually looking at the dates, but it does skip over the 'Closed' values. If there are potentially other values in row 2 to the left of the latest date, then this formula will return that value instead.

*Please note this is an array formula. After you enter the formula (but before you press the Enter key), press and hold the Ctrl and Shift keys, then press the Enter key. You will know when you have done this correctly because curly brackets {} will automatically appear around the formula.

Andrew

 

[bebo021999]

Hi, how can i nest up all the IF?
a b c d e f
1 19/7 18/7 17/7 16/7 15/7 14/7
2 Closed 82.06 82.42 83.00 81.00 85.00
I want my cell to pick up the value at roll 2 with the latest date. but if my cell is value "Closed" pick up the previous date with a value in it.
and if my cell a2 and b2 is closed pick up c2 and so on.
pls advise.

If date serial is sorted Z->A order like your':
=INDEX(A2:F2,MATCH(1,IF(A2:F2="Closed",0,1),0)) - CTRL-SHIFT-ENTER

If date serial is sorted A->Z:
=INDEX(A2:F2,MATCH(MAX(A2:F2)+1,A2:F2,1))

 

[josaiah]

alright thanks alot guys,

another question, as i will be adding a new date each day by inserting a column.

how do i freeze my formula to make my formula freeze to column B to H only?

 

[bebo021999]

alright thanks alot guys,

another question, as i will be adding a new date each day by inserting a column.

how do i freeze my formula to make my formula freeze to column B to H only?

Instead of: B1:H1
you can use $B$1:$H$1 to freeze

 

[josaiah]

Instead of: B1:H1
you can use $B$1:$H$1 to freeze

i've tried applying the $ sign to the formula, but it not effective.

after i insert a new column, the formula

Before
=INDEX(prev!$B$2:$H$2,MATCH(1,IF(prev!$B$2:$H$2="Closed",0,1),0))

After i insert a new column.
=INDEX(prev!$C$2:$I$2,MATCH(1,IF(prev!$C$2:$I$2="Closed",0,1),0))

can we insert a (INDIRECT) ?

 

[bebo021999]

i've tried applying the $ sign to the formula, but it not effective.
after i insert a new column, the formula
Before
=INDEX(prev!$B$2:$H$2,MATCH(1,IF(prev!$B$2:$H$2="Closed",0,1),0))
After i insert a new column.
=INDEX(prev!$C$2:$I$2,MATCH(1,IF(prev!$C$2:$I$2="Closed",0,1),0))
can we insert a (INDIRECT) ?

Now i understand your problem.
In your original, insert 1 more column between col B&C.
Copy (by Ctrl-C) your current data from col B to next col (new C col). Clear B column's contain.
Now your formular always include col B which is left blank.
Form now on, insert btw B & C to get new column for new date.
(B column is alway blank, you can hide it if you want)

 

[josaiah]

Now i understand your problem.
In your original, insert 1 more column between col B&C.
Copy (by Ctrl-C) your current data from col B to next col (new C col). Clear B column's contain.
Now your formular always include col B which is left blank.
Form now on, insert btw B & C to get new column for new date.
(B column is alway blank, you can hide it if you want)

I've tried ur way, now my value become 0.

I need my formula from column "B:H" column only.

 

[bebo021999]

I've tried ur way, now my value become 0.
I need my formula from column "B:H" column only.

Make sure:
* Column B have no data. Your main data start from C to G
* Insert new column btw B & C
*Replace with new formula below:
=INDEX(B2:G2,MATCH(1,IF(ISNUMBER(B2:G2),1,0),0))

Att file: http://www.megaupload.com/?d=LX9GQ4KZ

 

[Andrew Fergus]

Did you try the formula per my first post? It searches for the left-most value (such that the last column is irrelevant) and it doesn't matter if you insert new columns.

Andrew