Excel Formula Problem

Taff36 12:23 22 Dec 03
Locked

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?

VoG II 12:39 22 Dec 03

OR(E2 451,F2 451) looks wrong (missing equals signs)?

Taff36 12:47 22 Dec 03

The formula didn`t paste correctly VoG - it is actually "less than".

VoG II 13:11 22 Dec 03

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

e.g. =IF(OR(E2>601,F2>601),E39*D6,0)

and see if the results make sense.

Taff36 14:24 22 Dec 03

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

tbh72 15:25 22 Dec 03

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.

tbh72 16:03 22 Dec 03

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))

You could try this one?

tbh72 16:11 22 Dec 03

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.

pc moron 16:40 22 Dec 03

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:

=IF(AND(AND(\$E\$2>=200,\$E\$2<=450),AND(\$F\$2>=200,\$F\$2<=450)),\$E\$39*D1,0)

Substitute each of your ranges in the above formula (one formula per range) and sum the results to arrive at a final answer.

Taff36 16:42 22 Dec 03

Point taken. e-mail attachment on the way to you. Thanks for the offer.

tbh72 01:57 23 Dec 03

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.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

Apple Watch 2 review | Apple Watch Series 2 review: New Apple Watch is faster, brighter, water-resit…