negative numbers as a zero

[Guest]

AFTER CREATING A FORMULAR USING 3 CELLS I NEED TO MAKE SURE THAT ANY NEGATIVE
NUMBER IS SHOWN AND CALCULATED AS ZERO WHICH I KNOW HOW TO DO BUT I NEED TO
ALSO BE ABLE TO ENTER A MAXIMUM EG

=MAX(C4-D4,0) WILL GIVE ME A ZERO NUMBER BUT HOWE DO I CREAT A MAXIMUM
i WANT THE ANSWER NOT TO EXCEED 4300 AND THE MINIMUM TO BE 0
THANKYOU

 

[Roger Govier]

Hi Garry
Try
=MIN(MAX(C4-D4,0),4300)

 

[daddylonglegs]

Try

=MEDIAN(0,C4-D4,4300)

 

[Roger Govier]

Hi daddylonglegs

Nice solution!!

--
Regards

Roger Govier


"daddylonglegs"
message
news:[email protected]...

 

[Guest]

Where you currently have =your_formula, instead use
=min(4300,max(0,your_formula))
Evaluate from the inside out: max(0,xx) puts a floor of 0 on the result;
then min(4300,yy) applies a ceiling of 4300.

 

[Guest]

thanks so much, works a treat, saved me so much work, buy you a drink one
day ,, thanks again, sure shows the value of this type of site, will
certainly visit more now i have found it and hopefully may even be able to
pass on the little knowledge i have