assistance with if and choose

jacknc

Board Regular
Joined
Nov 28, 2005
Messages
75
Office Version
  1. 365
  2. 2019
I apparently messed this up and unfortunately saved before I could go back. Any assitance would be greatly appreciated. I had working perfectly earlier today.

scenario
g2 = yes or no
q2=1-5
r2 always =0 or 1
g41 is a multiplier 2-10
h41 is whatever number i choose
t2 is supposed to come up value (this will always be same regardless of g2 - yes or no
currently formula for t2 = =IF($K2>=$K$44,IF(G2="no",CHOOSE(R2*6+Q2+2,0,0,0,7,100,1000000,4,4,7,100,50000,1000000,$H$41),IF($G2="yes",CHOOSE(R2*6+Q2+1,0,0,0,7,100,1000000,4,4,7,100,50000,1000000,$H$41),0)),"")
so for example if q2 =5 and r2= 0 then total for t2=1,000000 - which it does - again I realize I need to clean code which I believe would be just to take the if g2 portions out. Which I can do.

T2 appears to be working.

My issue is with u2. Only values placed in u column will be if g2=yes based on multiplier in g41 basically I need exact format as above. Here is what I did for u2
=IF($K2>=$K$44,IF(T2>50,IF(G2="yes",CHOOSE(R2*6+Q2+1,0,0,0,7*$G$41,100*$G$41,50000*$G$41,1000000*2,4*$G$41,4*$G$41,7*$G$41,100*$G$41,50000*$G$41,1000000*2,$H$41)-$T2,0),0),"")

Issue is that you will see in this example that it always returns a negative # if g2=yes So in this example q2=5 and r2=0 I want total to be without multiplier in t2 always and use multiplier MINUS value from t2 for u2 for example you will see if choose = 1000000*2 if multiplier instead of normal multiplier. So 1,000000 would be in t2 and 1,000000 since I am trying to subtract u2-t2, but currently it shows 1000000 (which is what I want, but based on *2 other 1,000000 needs to be in u2.



I also need assistance figuring a way that if u2=h41 then t2 will always = 0 or "" within the t2 formula.
Again I appreciate all of the learning from this site, and always appreciate the assistance!

Thank you,
Jack
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jacknc

Board Regular
Joined
Nov 28, 2005
Messages
75
Office Version
  1. 365
  2. 2019
UPDATE:
Spent most of night pulling hair out! Issue is that you will see in this example that it always returns a negative # in u2 if g2=yes So in this example q2=5 and r2=0 I want total to be without multiplier in t2 always and use multiplier, except for this choose MINUS value from t2 for u2 for example you will see if choose = 1000000*2 if multiplier instead of normal multiplier. So 1,000000 would be in t2 and 1,000000 in u2 since I am trying to subtract u2-t2, but currently it shows 1000000 in t2 (which is what I want, but based on *2 other 1,000000 needs to be in u2.



I also need assistance figuring a way that if u2=h41 then t2 (q2=5 and r2=1, cause it will be amount of of h41)will always = 0 or "" within the u2 formula.
Again I appreciate all of the learning from this site, and always appreciate the assistance!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,882
Members
413,947
Latest member
gizmolucy

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