return a minimum value of 1, except when multiplied by an empty ce

[Ali]

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

 

[Ron Rosenfeld]

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

 

[Ali]

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

 

[Shane Devenshire]

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.

 

[Ali]

Hi Ron
Just to make this more difficult. i am using =IF($B3="",0,Max,(1,C$2*$B3))
which is working but now have extra hassle. It is returning a minimum of 1
except if cell B3 is empty which is great. however, I have a problem as
occassionally cell C2 is Zero. This means no guests are travelling so I DO
NEED a zero value and not a minimum of 1 otherwise they are buying
ingredients for non existent guests. Any way I can change it to include
this issue as well? thanks.
Shane, see you have posted but i cannot see it for some reason. happens to
me often.

 

[Ali]

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
Hi Ron, this is all fine, thanks I am using your first formula. however my problem is that should cell C2 actually be a zero, then i DO NEED a return of a zero value and not a minimum of 1. ONLY if row C ever has a zero. A zero here indictes zero guests so we want food to reflect a zeo rather than minimum 1 otherwise we are buying food for non existent guests. thanks can this be adjusted or not possible?

 

[David Biddulph]

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

 

[Ali]

Hi David, many thanks got it to work.
Cheers
Ali