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

--
Thanks,
Cheryl

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

Hope this helps.
--
John C


"Cheryl" wrote:

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

--
Thanks,
Cheryl

Perfect, thanks!!!
--
Thanks,
Cheryl


"John C" wrote:

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

Hope this helps.
--
John C


"Cheryl" wrote:

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

--
Thanks,
Cheryl

Thanks for the feedback. Don't forget to check 'yes' below. :)
--
John C


"Cheryl" wrote:

Perfect, thanks!!!
--
Thanks,
Cheryl


"John C" wrote:

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

Hope this helps.
--
John C


"Cheryl" wrote:

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

--
Thanks,
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?



--
Thanks,
Cheryl


"John C" wrote:

Thanks for the feedback. Don't forget to check 'yes' below. :)
--
John C


"Cheryl" wrote:

Perfect, thanks!!!
--
Thanks,
Cheryl


"John C" wrote:

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

Hope this helps.
--
John C


"Cheryl" wrote:

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

--
Thanks,
Cheryl

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)

--
John C


"Cheryl" wrote:

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?



--
Thanks,
Cheryl


"John C" wrote:

Thanks for the feedback. Don't forget to check 'yes' below. :)
--
John C


"Cheryl" wrote:

Perfect, thanks!!!
--
Thanks,
Cheryl


"John C" wrote:

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

Hope this helps.
--
John C


"Cheryl" wrote:

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

--
Thanks,
Cheryl