Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat
We`re trying to get Excel to calculate a labour cost based on percentage increases from one set of dimensions. This is our formula:
E39+IF(OR(E2>601,F2>601),E39*D6,0)+IF(OR(E2>701,F2>701),E39*D7,0)+IF(OR(E2>801,F2>801),E39*D8,0)+IF(OR(E2>901,F2>901),E39*D9,0)+IF(OR(E2>1001,F2>1001),E39*D10,0)+IF(OR(E2 451,F2 451),E39*D4,0)
E39 is a labour cost – E2 & F2 are dimensions - D6 to D8 are percentage increases dependant on the sizes in millimeteres.
By changing the dimensions in E2 & F2 the resultant labour cost should change but the formula above seems to give a cumulative result. Any suggestions please?
The formula didn`t paste correctly VoG - it is actually "less than".
I don't have time to look at this in detail at the moment (work!).
Try breaking the formula down into smaller chunks in separate cells
and see if the results make sense.
As you suggested the individual chunks record the right answers as the sizes are altered but of course they are adding them up cumulatively - exactly as we`ve told the formula to do!
I need the formula to return a value of the labour cost plus the percentage for sizes in E2 and F2 that are in the ranges 200 - 450, 451 - 500, 501 - 600, 601 - 700 etc
Breaking the formula down & generating the result's to several cells would be the most sensible solution. I would be happy to look at it if you e-mail me the file or relevant working data.
You could try this one?
I should explain incase the fornula is errornous, by your own admission you say you are not getting the desired result as each part of the formula is being added together. I have encpasulated the entire formula in bracets having removed the + addition functions you've included.
Obviously, it's easier to work directly on a work sheet than it is to "pluck" a formula out of the air on a forum.
You could maybe solve it using a nested IF formula but having two values to check (E2 and F2)will make the formula a nightmare.
It's better to break the problem down by testing each range seperately and then summing the result to get a final answer.
This formula will work for each of your ranges:
Substitute each of your ranges in the above formula (one formula per range) and sum the results to arrive at a final answer.
Point taken. e-mail attachment on the way to you. Thanks for the offer.
I have taken a look at the email, disregarding all the additional information am I right in assuming that the figure you are trying to produce is the basic cost + the additional percentage cost based on the two cells with the dimensions.
They are currently set at 450 & 1100 which means using the above assumption cell I5 = 321.99
Crosses his fingers in the hope that he has understood the problem correctly, that being the case I would simplify the formula to use lookup to either add or subtract the relevant discount.
Wait's with baited breath
This thread is now locked and can not be replied to.