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

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?


--
Thanks,
Cheryl


"John C" wrote:

> 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

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

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

--
Thanks,
Cheryl


"Cheryl" wrote:

> 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?
>
>
> --
> Thanks,
> Cheryl
>
>
> "John C" wrote:
>
> > 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

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

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

--
Thanks,
Cheryl


"Cheryl" wrote:

> I got it!!!! It's E2: =D2/(MAX(C2,1))
>
> Thanks so much for your help John!!!! Very, very helpful!
>
> --
> Thanks,
> Cheryl
>
>
> "Cheryl" wrote:
>
> > 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?
> >
> >
> > --
> > Thanks,
> > Cheryl
> >
> >
> > "John C" wrote:
> >
> > > 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

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

--
John C


"Cheryl" wrote:

> I am so close to having it be perfect...
>
> Any way to show 0 activites done, 0 activites required = 100%??? (currently
> shows 0%)
>
> --
> Thanks,
> Cheryl
>
>
> "Cheryl" wrote:
>
> > I got it!!!! It's E2: =D2/(MAX(C2,1))
> >
> > Thanks so much for your help John!!!! Very, very helpful!
> >
> > --
> > Thanks,
> > Cheryl
> >
> >
> > "Cheryl" wrote:
> >
> > > 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?
> > >
> > >
> > > --
> > > Thanks,
> > > Cheryl
> > >
> > >
> > > "John C" wrote:
> > >
> > > > 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