Ceiling function help

Hi All,

I am currently using the formula below to calculate 10% of a total, it rounds up to the nearest whole number to ensure that at least one spare part is supplied.

=CEILING((F193*0.1),1)

Is there a way to change this to give an output of 3 as a minimum or 10%? Currently if I input 8 the calculation gives me a result of 1, if I input 30 I get a result of 3. I need to ensure that the result is at least 3.

thanks for your help

How about?

=IF(F193<30,3,CEILING((F193*0.1),1))

=if(f193=0,0,IF(F193<30,3,CEILING((F193*0.1),1)))

Might be worth encasing within say N and testing <= 0 given CEILING would otherwise fail (negatives)

=IF(N(F193)<=0,0,MAX(3,CEILING(F193*0.1,1)))

I think an error message for a negative number might actually be a good thing rather than trying to intercept it with a more complex formula.

There are situations where users may actually have to do some quality checking themselves. Not every data entry error needs to be intercepted by the formula.

In this case, if the user enters a negative number, the error message would point them to the fact that something about the entered number might be wrong. Using the N() function and using the <= will allow the user to happily enter wrong data and still not be made aware of it.

Well, that's certainly a theory...

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Click Edit on your first post, Go Advanced and change Prefix on Title to Solved.

As for the error - sure thing though if error notification is important you could consider using Data Validation if you wish to enforce numeric entry in the first instance [within specified boundaries].

using Data Validation if you wish to enforce numeric entry

... in which case the N() and <= become obsolete, too.

Agreed.

However, my point was/remains - if trapping the error visibly is warranted then using Validation is arguably the more logical approach (pref. to Conditional Formatting).

In cases where such a visible flag is not warranted (as presumed [incorrectly] here by me) - and instead one wants only a default value - then in such cases the use of N and <= is likely to be sufficient ... where precedents are formulae it would not be.