convert negative numbers to a zero

[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!!

 

[John C]

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

Hope this helps.

 

[Cheryl]

Perfect, thanks!!!
--
Thanks,
Cheryl

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

Hope this helps.

 

[John C]

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

 

[Cheryl]

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?

 

[John C]

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)

 

[Cheryl]

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?

 

[Cheryl]

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

Thanks so much for your help John!!!! Very, very helpful!

 

[Cheryl]

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

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

 

[John C]

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

--
John C

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

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

 

[Cheryl]

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