Nested IF And with Or statement

Caly

New Member
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

Attachments

  • image001.png
    image001.png
    21 KB · Views: 6

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
Can you please post your formula to the thread, rather than just an image.
 

Caly

New Member
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
Thanks for that.
How about
Excel Formula:
=IF(OR(H4="Apple",H4="Orange"),B4,IF(H4="Pear",D4,IF(H4="Grape",F4,"")))*IF(A4="",1,A4)
 

Caly

New Member
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
Can you please post some sample data using the XL2BB add-in
 

Caly

New Member
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=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

New Member
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
That worked ... thank you so much for your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,544
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,781
Members
416,940
Latest member
JohanT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top