Nested IF And with Or statement

[Caly]

Hi I have a statement where I keep getting an error. How do I write this formula?

column A is for a unit of issue conversion where is may say 2.
columns B, D, and F will have prices for different products
Column H will have the product to use and column I will have the price based on the product in column I
For the column I formula I am trying to say if Column A is not blank and Column H either says one of the two product types that may be in Column B to multiply the price by the column A amount and if column A is blank then just use the price for the product.

here is an image of what I am trying

 

[Fluff]

Can you please post your formula to the thread, rather than just an image.

 

[Caly]

My apologies. Please see below:

=if(and(or(A4<>"",H4="Apple",H4="Orange"),B4*A4,B4)),IF(AND(A4<>"",H4="Apple"),B4*A4,IF(AND(A4<>"",H4="Pear"),D4*A4,IF(AND(A4<>"",H4="Grape"),F4*A4,IF(H4="Apple",B4,IF(H4="Pear",D4,F4)))))

 

[Fluff]

Thanks for that.
How about

=IF(OR(H4="Apple",H4="Orange"),B4,IF(H4="Pear",D4,IF(H4="Grape",F4,"")))*IF(A4="",1,A4)

 

[Caly]

Thank you so much...that worked perfect.

If the u it conversion varies between the products, how can that be written in the formula?

so I’m trying to see if there is a different unit conversion by product how can that be written?

column a would be the unit conversion for apple or orange in column b
But then I need to have a separate unit conversion column for Pear and another for Grape

the unit conversion for pear would be in column d and for grape would be in column G

I was trying this but it is not working for me.
IF(OR(J4="Apple",J4="Orange"),B4*IF(A4="",1,A4),IF(J4="Pear",E4*IF(D4="",1,D4),IF(J4="Grape",H4,*IF(G4="",1,G4)

 

[Fluff]

Can you please post some sample data using the XL2BB add-in

 

[Caly]

Unit Conversion​	Apple or Orange​	Amount​	Unit Conversion​	Pear​	Amount​	Unit Conversion​	Grape​	Amount​	Final Type​	Final Price​	Formulas​
2​	$5.00​	EA​	3​	$6.00​	EA​	5​	$7.00​	EA​	Apple​	$10.00​	IF(AND(A2<>"",H2="Apple"),B2*A2,IF(AND(A2<>"",H2="Pear"),D2*A2,IF(AND(A2<>"",H2="Grape"),F2*A2,IF(H2="Apple",B2,IF(H2="Pear",D2,F2)))))
​	$8.00​	PK​	​	$9.00​	PK​	​	$10.00​	PK​	Grape​	$10.00​	IF(AND(A3<>"",H3="Apple"),B3*A3,IF(AND(A3<>"",H3="Pear"),D3*A3,IF(AND(A3<>"",H3="Grape"),F3*A3,IF(H3="Apple",B3,IF(H3="Pear",D3,F3)))))
2​	$2.00​	PK​	4​	$3.00​	PK​	6​	$4.00​	PK​	Orange​	$4.00​	IF(OR(J4="Apple",J4="Orange"),B4*IF(A4="",1,A4),IF(J4="Pear",E4*IF(D4="",1,D4),IF(J4="Grape",H4,*IF(G4="",1,G4)

Thank you so much. Here it is

 

[Fluff]

How about

=IF(OR(J4="Apple",J4="Orange"),B4*IF(A4="",1,A4),IF(J4="Pear",E4*IF(D4="",1,D4),IF(J4="Grape",H4*IF(G4="",1,G4))))

 

[Caly]

That worked ... thank you so much for your help

 

[Fluff]

You're welcome & thanks for the feedback.