Simple but large formula that stopped my brain

nmccracken12

New Member
Joined
Oct 8, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
I need help with a massive formula. I will do my best to explain this and please let me know if you need any more information.

IF AE2 ="DI" AND AC = ~list below with formulas to add~ Then use AF2 to calculate laid in OR if AE2 = ~any other value~ THEN run this formula =IF(ISNUMBER(AJ2),(AH2*AJ2)+SUM(AI2,AL2)-(AK2),IF(ISBLANK(AJ2),SUM(AG2,AI2)-(AK2),""))

Spirits --- (T2*U2)/1000*0.264172)*((AB2*2)*13.5)+B23
Sparkling Wine --- (T2*U2)/1000*0.264172)*B17+(T2*U2)/1000*C17+B23
Wine<16.5 --- (T2*U2)/1000*0.264172)*B15+(T2*U2)/1000*C15+B23
Wine>16.5 --- (T2*U2)/1000*0.264172)*B15+(T2*U2)/1000*C15+B23
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I need help with a massive formula. I will do my best to explain this and please let me know if you need any more information.

IF AE2 ="DI" AND AC = ~list below with formulas to add~ Then use AF2 to calculate laid in OR if AE2 = ~any other value~ THEN run this formula =IF(ISNUMBER(AJ2),(AH2*AJ2)+SUM(AI2,AL2)-(AK2),IF(ISBLANK(AJ2),SUM(AG2,AI2)-(AK2),""))

Spirits --- (T2*U2)/1000*0.264172)*((AB2*2)*13.5)+B23
Sparkling Wine --- (T2*U2)/1000*0.264172)*B17+(T2*U2)/1000*C17+B23
Wine<16.5 --- (T2*U2)/1000*0.264172)*B15+(T2*U2)/1000*C15+B23
Wine>16.5 --- (T2*U2)/1000*0.264172)*B15+(T2*U2)/1000*C15+B23
How does AC = one of the formulas? Can you provide a small sample of your data or explain in further detail how the formula is supposed to work?
 
Upvote 0
Try something like this. The formulas you listed at the bottom are not valid formulas since the opening and closing parentheses do not match. There is also no need for so many parentheses. So, to make a start I have removed all those parentheses. Also, not all the parentheses are needed in the formula for of AE2 is any other value are needed but at least they matched so I left them as-is.

You will need to check the results and adjust some parts of this formula if required but I think this should get you started.
Put this formula in row 2 and copy down.

Excel Formula:
=IF(AE2="DI",CHOOSE(MATCH(AC2,{"Spirits","Sparkling Wine","Wine<16.5","Wine>16.5"},0),T2*U2/1000*0.264172*AB2*2*13.5+B23,T2*U2/1000*0.264172*B17+T2*U2/1000*C17+B23,T2*U2/1000*0.264172*B15+T2*U2/1000*C15+B23,T2*U2/1000*0.264172*B15+T2*U2/1000*C15+B23),IF(ISNUMBER(AJ2),(AH2*AJ2)+SUM(AI2,AL2)-(AK2),IF(ISBLANK(AJ2),SUM(AG2,AI2)-(AK2),"")))
 
Upvote 0
How does AC = one of the formulas? Can you provide a small sample of your data or explain in further detail how the formula is supposed to work?
It's really hard to explain. AC = Spirits,Wine,Sparkling, etc... When it equals that, I need the fomula that I put beside it to run. So if AC=Spirits it would know to do this (T2*U2)/1000*0.264172)*((AB2*2)*13.5)+B23...

Does that make sense? This file I have is really large, but I will attach it if you think that will help.
 
Upvote 0
It's really hard to explain. AC = Spirits,Wine,Sparkling, etc... When it equals that, I need the fomula that I put beside it to run. So if AC=Spirits it would know to do this (T2*U2)/1000*0.264172)*((AB2*2)*13.5)+B23...

Does that make sense? This file I have is really large, but I will attach it if you think that will help.
Sure, it makes sense. I just didn't make the connection right away that's what you meant. See Peter's suggested formula in post #3.
 
Upvote 0
Try something like this. The formulas you listed at the bottom are not valid formulas since the opening and closing parentheses do not match. There is also no need for so many parentheses. So, to make a start I have removed all those parentheses. Also, not all the parentheses are needed in the formula for of AE2 is any other value are needed but at least they matched so I left them as-is.

You will need to check the results and adjust some parts of this formula if required but I think this should get you started.
Put this formula in row 2 and copy down.

Excel Formula:
=IF(AE2="DI",CHOOSE(MATCH(AC2,{"Spirits","Sparkling Wine","Wine<16.5","Wine>16.5"},0),T2*U2/1000*0.264172*AB2*2*13.5+B23,T2*U2/1000*0.264172*B17+T2*U2/1000*C17+B23,T2*U2/1000*0.264172*B15+T2*U2/1000*C15+B23,T2*U2/1000*0.264172*B15+T2*U2/1000*C15+B23),IF(ISNUMBER(AJ2),(AH2*AJ2)+SUM(AI2,AL2)-(AK2),IF(ISBLANK(AJ2),SUM(AG2,AI2)-(AK2),"")))
Thank you for this. I have updated it to the proper cells that I need and will paste below. The issue I am having at this point is that when I change the drop down on AC from spirits to anything else, I get N/A returned. It seems like spirits is the only one working. Also, if I mark AE = DI it is not having the expected outcome. I need it to use the pricing that I have in column AF2 when AE2=DI. Any ideas on what I need to change? Expected outcome for AE2=DI would be AF2+ the formulas that are dependent on being marked wine, spirit, etc... on AC2. Each product type (wine, spirit) uses it's on unique formula that I posted above.

=IF(AE2="DI",CHOOSE(MATCH(AC2,{"Spirits","Sparkling Wine","Wine<16.5","Wine>16.5"},0),T2*U2/1000*0.264172*AB2*2*13.5+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B17+T2*U2/1000*'FREIGHT ZONES'!C17+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B15+T2*U2/1000*'FREIGHT ZONES'!C15+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B15+T2*U2/1000*'FREIGHT ZONES'!C15+'FREIGHT ZONES'!B23),IF(ISNUMBER(AJ2),(AH2*AJ2)+SUM(AI2,AL2)-(AK2),IF(ISBLANK(AJ2),SUM(AG2,AI2)-(AK2),"")))
 
Upvote 0
We don't have any sample data or expected outcomes, the formulas have a lot of input cells and also some decimal values that are not just very simple ones so it is pretty hard to try to work out what you want or what is going wrong.

Expected outcome for AE2=DI would be AF2+
Sorry, I did not understand that from post 1.

Try this one.

=IF(AE2="DI",AF2+CHOOSE(MATCH(AC2,{"Spirits","Sparkling Wine","Wine<16.5","Wine>16.5"},0),T2*U2/1000*0.264172*AB2*2*13.5+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B17+T2*U2/1000*'FREIGHT ZONES'!C17+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B15+T2*U2/1000*'FREIGHT ZONES'!C15+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B15+T2*U2/1000*'FREIGHT ZONES'!C15+'FREIGHT ZONES'!B23),IF(ISNUMBER(AJ2),(AH2*AJ2)+SUM(AI2,AL2)-(AK2),IF(ISBLANK(AJ2),SUM(AG2,AI2)-(AK2),"")))

If that works then since you are using 365, this shorter version should also work.

=LET(w,T2*U2/1000,k,w*0.264172,a,'FREIGHT ZONES'!B23,b,'FREIGHT ZONES'!B15,c,'FREIGHT ZONES'!C15,IF(AE2="DI",AF2+CHOOSE(MATCH(AC2,{"Spirits","Sparkling Wine","Wine<16.5","Wine>16.5"},0),k*AB2*2*13.5+a,k*'FREIGHT ZONES'!B17+w*'FREIGHT ZONES'!C17+a,k*b+w*c+a,k*b+w*c+a),IF(ISNUMBER(AJ2),AH2*AJ2+SUM(AI2,AL2)-AK2,IF(ISBLANK(AJ2),SUM(AG2,AI2)-AK2,""))))
 
Last edited:
Upvote 0
We don't have any sample data or expected outcomes, the formulas have a lot of input cells and also some decimal values that are not just very simple ones so it is pretty hard to try to work out what you want or what is going wrong.


Sorry, I did not understand that from post 1.

Try this one.

=IF(AE2="DI",AF2+CHOOSE(MATCH(AC2,{"Spirits","Sparkling Wine","Wine<16.5","Wine>16.5"},0),T2*U2/1000*0.264172*AB2*2*13.5+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B17+T2*U2/1000*'FREIGHT ZONES'!C17+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B15+T2*U2/1000*'FREIGHT ZONES'!C15+'FREIGHT ZONES'!B23,T2*U2/1000*0.264172*'FREIGHT ZONES'!B15+T2*U2/1000*'FREIGHT ZONES'!C15+'FREIGHT ZONES'!B23),IF(ISNUMBER(AJ2),(AH2*AJ2)+SUM(AI2,AL2)-(AK2),IF(ISBLANK(AJ2),SUM(AG2,AI2)-(AK2),"")))

If that works then since you are using 365, this shorter version should also work.

=LET(w,T2*U2/1000,k,w*0.264172,a,'FREIGHT ZONES'!B23,b,'FREIGHT ZONES'!B15,c,'FREIGHT ZONES'!C15,IF(AE2="DI",AF2+CHOOSE(MATCH(AC2,{"Spirits","Sparkling Wine","Wine<16.5","Wine>16.5"},0),k*AB2*2*13.5+a,k*'FREIGHT ZONES'!B17+w*'FREIGHT ZONES'!C17+a,k*b+w*c+a,k*b+w*c+a),IF(ISNUMBER(AJ2),AH2*AJ2+SUM(AI2,AL2)-AK2,IF(ISBLANK(AJ2),SUM(AG2,AI2)-AK2,""))))
Thank you, Peter. This is amazing! It is working as expected. I have one question for you. If I wanted the option to use real time conversions on column AG2 for USD/EUR, how would I go about that? I have a column that has the conversion rates in real time using the currencies data type, but I do not know how to plug that in.

'FREIGHT ZONES'!B25 - contains the conversion rate
AT2 contains a drop down for USD or EURO
When AT2 = EURO column AF2 would display currency in EURO's (this is manually type, so it does not need to have any conversions added to it), ~BUT~ Column AM2 would continue to display dollars with the proper conversions using the rate in 'FREIGHT ZONES'!B25.

Is this possible? If you can think of another way to do this, I am all ears!
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

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
Back
Top