Greater than and less than then multiply by for multiple cells


Hi Team,

i'm looking for a formula in my attached spreadsheet.

I'd like it to give me a total amount at V7 based on the number input into G7, being greater than F7 but less than L7, then G7 to be multiplied by the figure in H7. Also in the same equation, it needs to take into account if the number in G7 is greater than L7 but less than Q7 then multiply by M7, and if the number in G7 is greater than Q7 then multiply by R7

Also maybe if the number in G7 is less than the number in F7 then multiply by E7

Then I should be able to drag and drop

Hope that makes sense. I'm ok at basic equations, but this is way over my head.



Selected Answer

 Your workbook wasn't attached to your question. I don't know if it would have helped. This is the formula I thought up. Please try it.

=$G7 * IF($G7 <= $F7, $E7, IF($G7 < $L7, $H7,IF($G7 >= $Q7, $R7, IF($G7 > $L7, $M7,0))))

I made two logical changes to your description. You say what to do if G7 is larger than F7 or smaller than F7 but not what should happen when it is equal to F7. Same thing between G7 and Q7. My formula chooses to multiply with E7 if G7 = F7 and multiply with R7 if G7 = Q7.

My formula will multiply with 0 (producing a result of zero) if none of the conditions are met. If you wish to have that result instead of the changes I made, remove the equal sign from >= and <= in the above formula or accommodate the possible equality by adding the equal sign in the subsequent IF.

For your understanding, the formula takes G7 and finds a multiplier for it. For this purpose it first compares G7 with F7. If G7 <= F7 a result was found. Else F7 must be larger. That leads to the second IF where the formula checks if G7 is smaller than L7 - IF($G7 < $L7, $H7 - if so a multiplier is found (you might add an euqal sign here, too). If this condition  isn't met either the whole thing is basically repeated with Q7 and L7. Here is the plain language formula design which I encoded.

IF G7 < F7 Then G7 * E7
Else IF G7  > F7 And < L7 Then G7 * H7
Else IF G7 > Q7 Then G7 * R7
Else IF G7 < Q7 And  > L7 Then G7 * M7


Thankyou for your quick reply, made the adjustments as you suggested. and all works fine. Cheers
derekklau (rep: 2) Mar 30, '18 at 9:11 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login