Help with "If greater than = X, but less than = calculation" statement.

[PatGubbins]

Hi All,

I have been trying to figure this one out and cant get it.

I need my formula to do things. 1 if A1*B1=<20 then make it equal 20. which I have worked out =IF(D14*F14<20,"20",""), I then need if A2*B2=>20 that is shows the value of the multiplication. which I have =IF(D15*F15>=20,D15*F15,"") which is blank if less than 20. How can i put them together.

Thanks in advance!

 

[RobP]

Hi welcome,

looking at what you've written, you refer to A1*b1, which you say was worked out with your formula referring to D14*f14... then I get a little lost with your explanation on the next part. Would you mind re-reading it, and explaining a little clearer in terms of your need please ? I dont understand the relationship between A2*B2 and your formula D15*F15 ?

maybe just write in plain english what you want a formula to do, and which cells to act on ?

many thanks
Rob

 

[Alex Blakenburg]

Assuming you are talking about the same row for both conditions try:

=MAX(A1*B1,20)

 

[PatGubbins]

Hi welcome,

looking at what you've written, you refer to A1*b1, which you say was worked out with your formula referring to D14*f14... then I get a little lost with your explanation on the next part. Would you mind re-reading it, and explaining a little clearer in terms of your need please ? I dont understand the relationship between A2*B2 and your formula D15*F15 ?

maybe just write in plain english what you want a formula to do, and which cells to act on ?

many thanks
Rob

Hi Rob, Sorry just realised I have copied the formula and referred to different cells.

So what I would like the formula to do is if I have B1=A1*C1 and if it is less than 20 than B1 shows as 20. but if I have B2=A2*C2 and it is greater than 20 I want it to show as the calculated figure.

So if it were B1=2*5 which is 10, I want the formula to supersede it and show 20 which I have achieved using this. IF(A1*C1<20,"20",""). Then if B2=9*5 which is 45 i want b2 to = 45.

I hope this makes sense, I would like IF(A1*C1<20,"20",""),IF(AND(A1*C1>20,"A1*C1"). If A1*C1= less than 20 then it equal 20 but is A1*C1 = more than 20 than it shows whatever the value calculate is.

Thanks!

 

[Alex Blakenburg]

Did you try my formula ?

 

[RobP]

thanks Pat - its as clear as mud

So based on what you are saying, actually you want a formula for B1, and a separate formula for B2 ? Or are you saying you have formulas for B1 and B2, and you are looking for a formula for another cell, based on B1 and B2 results ?

So B1 can be (from @Alex), or what you had already.

=Max(A1*C1,20)

B2 would have :

MAx(A2*C2,20)

Is that what you need ?
Rob

 

[PatGubbins]

Did you try my formula ?

Hi Alex,

Thank you for your help! Yes that worked!

 

[PatGubbins]

thanks Pat - its as clear as mud

So based on what you are saying, actually you want a formula for B1, and a separate formula for B2 ? Or are you saying you have formulas for B1 and B2, and you are looking for a formula for another cell, based on B1 and B2 results ?

So B1 can be (from @Alex), or what you had already.

=Max(A1*C1,20)

B2 would have :

MAx(A2*C2,20)

Is that what you need ?
Rob

Hi Rob,

Yes this worked. Thank you!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.