# convert negative numbers to a zero

C

#### Cheryl

Hi,

I need to set up a function that converts a negative number to a zero. For
example, I'm setting up a table to find out the amount of "required
activities" my employees should have based on sales, but I do not want the
number of "required activities" to be negative if they've sold a lot in a day.

For example: the employees are required to do 30 "activities" in a day. For
every sale they do, they are allowed 5 less activites. =30-(#of sales x 5)
However if they sell more than 6, the number becomes negative.

Thanks, I appreciate the help!!

=MAX(30-(#sales*5),0)

Hope this helps.

Perfect, thanks!!!
--
Thanks,
Cheryl

John C said:
=MAX(30-(#sales*5),0)

Hope this helps.

Thanks for the feedback. Don't forget to check 'yes' below.

You seem to be very knowledgeable about excel, so one more question

I'm trying to get their performance percentage now, but running into another
problem...

If # of required activities is 0, but they complete 10 activities, they
should be over 100%. However, I'm getting a "divide by zero" error because
10/0. Any suggestions of how I can fix this?

Setup:
B2: #sales
C2: =MAX(30-(B2*5),0) ... required activities
D2: activities
E2: =D2/(MAX(D2,1)) ... This would assign a 1 as a divisor if no activities
are required, so in your example below, this would mean 1000%. Effectively,
it gives 100% per activity done if they have no required activities. Note,
this is an arbitrary value, as the mathematical reality is that if 0
activities are required, and they do 1 activity, then they have done
infinitely more activities than required. If you want to show nothing, then
you could have the E2 formula like this:
=IF(C2=0,"",D2/C2)

I like this: E2: =D2/(MAX(D2,1)), however it's not showing 1000%, but 100%.

Do you know what I'm doing wrong?

I would also like it to show the different % for any # of activities done
over the 0 they were required to do...example 120%, 250% 1000%. Is that
possible with this function or is that getting to complicated?

I got it!!!! It's E2: =D2/(MAX(C2,1))

I am so close to having it be perfect...

Any way to show 0 activites done, 0 activites required = 100%??? (currently
shows 0%)

Try:
=MAX(D2,1)/(MAX(C2,1))

--
John C

Cheryl said:
I am so close to having it be perfect...

Any way to show 0 activites done, 0 activites required = 100%??? (currently
shows 0%)

You're the best!!! Thanks again for all your help!