return a cell value from multiple ifs

[splash]

Hi all

Can you offer assistance?

I'm trying to return a value from a cell but the multiple ifs required exceed the limit in the formula bar.

Essentially, in the example below:

Budget May-11 Jun-11 Jul-11 Aug-11

3.0% 3.0% 3.0%
3.0% 3.0%
3.0%

Column A needs to return the date from Row 1 when the 1st in a sequence of numbers appears on each particular row. So in the example above, Row 2 (cell A2) would return May-11, Row 3 (cell A3) would return Jun-11, Row 4 (A4) Jul-11, Row 4 (A5) would return "N/A" if no figures appear.

The multiple if function that I am trying to run seems to only be able to cope with 8 or so arguments (=IF(A2>0,A$1,if(B2>0,B$1,if(C2>0,C$1,if(D2>0,D$1,if(E2>0,E$1,...),"N/A"))

Any advice extremely gratefully received
Thanks in advance
Splash

 

[T. Valko]

Hi all

Can you offer assistance?

I'm trying to return a value from a cell but the multiple ifs required exceed the limit in the formula bar.

Essentially, in the example below:

Budget May-11 Jun-11 Jul-11 Aug-11

3.0% 3.0% 3.0%
3.0% 3.0%
3.0%

Column A needs to return the date from Row 1 when the 1st in a sequence of numbers appears on each particular row. So in the example above, Row 2 (cell A2) would return May-11, Row 3 (cell A3) would return Jun-11, Row 4 (A4) Jul-11, Row 4 (A5) would return "N/A" if no figures appear.

The multiple if function that I am trying to run seems to only be able to cope with 8 or so arguments (=IF(A2>0,A$1,if(B2>0,B$1,if(C2>0,C$1,if(D2>0,D$1,if(E2>0,E$1,...),"N/A"))

Any advice extremely gratefully received
Thanks in advance
Splash

Try this...

Book1
	A	B	C	D	E
1	_	May-11	Jun-11	Jul-11	Aug-11
2	May-11	55	11	14	_
3	Jun-11	_	8	55	_
4	Jul-11	_	_	88	_
5	#N/A	_	_	_	_
Sheet1

This array formula** entered in A2 and copied down:

=INDEX(B$1:E$1,MATCH(TRUE,ISNUMBER(B2:E2),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format in the Date style of your choice.

 

[splash]

Much obliged for your speedy reply

This option seems to do the trick, one quick follow up question... once the formula has been input as an array and copied into the relevant cells is there an easy means to update all the formulas other than updating each cell individually with Ctrl, Shift and Enter?

Once again thanks in advance
Splash

 

[T. Valko]

Much obliged for your speedy reply

This option seems to do the trick, one quick follow up question... once the formula has been input as an array and copied into the relevant cells is there an easy means to update all the formulas other than updating each cell individually with Ctrl, Shift and Enter?

Once again thanks in advance
Splash

That sounds like you have calculation set to manual.

Make sure calculation is set to automatic.

 

[splash]

Thanks again.
Splash

 

[T. Valko]

Thanks again.
Splash

You're welcome!