negative numbers as a zero
 

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

negative numbers as a zero
 

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

--
Regards

Roger Govier


"GARRYPRINCE" wrote in message
...
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

negative numbers as a zero
 

Try

=MEDIAN(0,C4-D4,4300)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=545298

negative numbers as a zero
 

Hi daddylonglegs

Nice solution!!

--
Regards

Roger Govier


"daddylonglegs"
wrote in
message
news:daddylonglegs.28c0lm_1148507701.1732@excelfor um-nospam.com...

Try

=MEDIAN(0,C4-D4,4300)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=545298

negative numbers as a zero
 

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.


"GARRYPRINCE" wrote:

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

negative numbers as a zero
 

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

"bpeltzer" wrote:

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.


"GARRYPRINCE" wrote:

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