Simple but large formula that stopped my brain

N

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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
nmccracken12 said:
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
Click to expand...
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
dreid1011 said:
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?
Click to expand...
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
nmccracken12 said:
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.
Click to expand...
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
Peter_SSs said:
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),"")))
Click to expand...
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.

nmccracken12 said:
Expected outcome for AE2=DI would be AF2+
Click to expand...
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
Peter_SSs said:
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,""))))
Click to expand...
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
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
322
Mouse7681
M
N
  • Solved
Messy Formula
Replies
2
Views
1K
Joe4
Joe4
M
  • Question Question
VBA Code to Copy multiple Columns from Source Sheet into 4 columns of Destination Sheet
Replies
1
Views
922
kevin9999
K
E
  • Solved
Dragging formula along columns with changing reference cells
Replies
4
Views
802
excel_lisa
E
L
  • Question Question
Any way to simplify the code
2
Replies
12
Views
1K
MARK858
MARK858

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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