Create a lower and upper estimate with a £200 minimum difference requirement

[EHastie]

Hi,

Hoping someone much more technical than me can help please. Apologies in advance if this is really simple

I am creating a quote range to provide to a client. For the Lower Estimate I've used CEILING to round up to the nearest £100. For the upper estimate it's is a 20% increase on the Lower which is again rounded up to the nearest £100. That all works okay.

I've got a problem in that the upper estimate should be a minimum of £200 more than the lower so if the lower estimate was say £400 the upper calculates as £500, I need this to be £600, if it was £600 the upper would need to be £800 to comply with the £200 minimum difference rule. Once you hit £1,000 it's obviously okay so I only need it to update the figure based on when the value doesn't comply with the requirement. I've tried IF functions and just made a mess!

Thanks in advance

[Glenn Kennedy]

Next time, read the yellow banner (top) and provide a sample sheet with expected answers... this is a guess based on your description.

=MAX(B2+200,CEILING(1.2*B2,100))

see file.

[sweep]

assuming your calculated upper estimate is in A1 and the lower estimate is in B1,

=MAX(A1,B1+200)

Does that help?

[EHastie]

Hi Glenn,

Apologies I thought I'd saved the sheet, I will make sure it's saved for next time. But that is exactly what I needed, thanks for taking the time to help me.

Thanks
Eileen

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.