Hi,

If Max(1,Sum($C$1*B2)) works except for the blank cells then you can use

=IF($B2,MAX(1,C$1*$B2),0)

No need to SUM(C1*B2) here or in any formula.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ali" wrote:


Hi Ron
Sorry if you did not get all the correct info, but your answer has still
helped and it works fine. many thanks
Ali
"Ron Rosenfeld" wrote:

On Sun, 22 Mar 2009 02:39:00 -0700, Ali wrote:

Hi there. i have a sheet containing various stock items, and in order to
calculate the amount per item that is needed per hotel, I have a cell where a
per person per day amount (in brackets) is multiplied by a set unit for that
ingredient
A B C D E
Hotel 1 Hotel 2 Hotel 3
1) Item Unit (6.1) (1.38) (9.22)
2) Coco 0.2 1 1 2
3) Custard 0.25 2 2 2
4) Crème 0.1 1 1 1

So for example cell C2 = $C$1*B2
Because the minimum needed for just 1 guest cannot be less than one i have
changed all the formulas to be example
C2 = Max(1,Sum($C$1*B2))
this is great as it gives me a minimum of 1 for certain items that would
normally be rounded off to zero. This is great.
However, my problem is that certain items still have UNITS that need to be
determined. So example Creme, cell B4 would still be empty as the per person
per day amount is still being calculated.
I would like these cells to reflect a zero (as they are multiplying empty
cells) rather than the minimum of 1. if it is minimum 1 the stores will go
ahead and order this, thinking it is correct, rather than if it shows a zero,
the store man will realise there is a problem and double check the spread
sheets, calculate the missing unit and then order.
Hope some-one can assist.
Thanks

I don't understand some of your logic as your results do not compute from the
formula given. For example,

C2: 6.1*0.2 = 1.22 not 1
E3: 9.22*.25 = 2.305 not 2

And also the SUM function is completely unnecessary.

So it would appear you are rounding your results. Is that what you really want
to do? Or do you want to round UP to the next integer?

But, in general, to make a special case if the Unit is not filled in, you can
use a construct such as:

=IF($B3="",0,MAX(1,C$2*$B3))

If you want to round to the closest integer, then:

=IF($B3="",0,ROUND(MAX(1,C$2*$B3),0))

If you want to round up to the next integer, then:

=IF($B3="",0,CEILING(MAX(1,C$2*$B3),1))

--ron