Nested If statement

[francesshakira]

I need to write a formula to help me return a value based on the following:


Price 2 Price 5 Price 3 Price 1 Price 4
Product A N/A 158 - 168 158



If Price 1 has a value greater than zero, then return that value, otherwise, select price 2...if not, then price 3...if not, then price 4...finally price 5.

I tried a nested if statement, but it's not working.

Help!

 

[njimack]

Welcome to the board. Something like this?

Excel Workbook
	A	B	C	D	E	F
1	Price 2	Price 5	Price 3	Price 1	Price 4
2	150	158	0	0	158	150
Sheet1

 

[jdm21788]

when you say "if not," do you mean > 0? if so, then:

=if(price1>0,price1,if(price2>0,price2,if(price3>0,price3,if(price4>0,price4,price5))))

 

[francesshakira]

This is the formula that I had originally written, but it doesn't work if there are is an N/A for one of the prices.

I have 5 different sources for prices that I did Vlookups for. That is why I have some N/A values.

 

[njimack]

2nd attempt, to deal with N/A...

Excel Workbook
	A	B	C	D	E	F
1	Price 2	Price 5	Price 3	Price 1	Price 4
2	N/A	158	0	0	158	158
Sheet1

 

[T. Valko]

I need to write a formula to help me return a value based on the following:


Price 2 Price 5 Price 3 Price 1 Price 4
Product A N/A 158 - 168 158



If Price 1 has a value greater than zero, then return that value, otherwise, select price 2...if not, then price 3...if not, then price 4...finally price 5.

I tried a nested if statement, but it's not working.

Help!

If you can sort the prices like below:

Book1
	A	B	C	D	E	F
1	_	Price 1	Price 2	Price 3	Price 4	Price 5
2	Product A	168	N/A	-	158	158
Sheet1

This array formula**:

=INDEX(B2:F2,MATCH(TRUE,ISNUMBER(B2:F2),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.

 

[jdm21788]

njimack and T. Valko are probably on to more sound suggestions than mine, but adding n() around all of the cell references will treat N/A (or any other non-number) as 0:

=IF(N(A1)>0,A1,IF(N(A2)>0,A2,IF(N(A3)>0,A3,IF(N(A4)>0,A4,A5))))

 

[francesshakira]

Thank you so much everyone. All of the formulas were very helpful!

 

[T. Valko]

Thank you so much everyone. All of the formulas were very helpful!

You're welcome. We appreciate the feedback!

 

[francesshakira]

Welcome to the board. Something like this?

Excel Workbook
	A	B	C	D	E	F
1	Price 2	Price 5	Price 3	Price 1	Price 4
2	150	158	0	0	158	150
Sheet1

Hi,

Will this formula only work if the column headers are "Price 1", "Price 2", etc.?