Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily sale
is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9 he gets
105%. if it is 15.0-15.49, he gets 110% and so on. But I can not put so many
conditions in one formula...

so basically each .5 increase in the figure will increase the %age by 5. But
if it is less than .5 increase, it stays where it is. so if it is 14.6 or
14.7...14.9, it still remains 105% and the moment it reaches 15, the %age
increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

"Gaurav" wrote:
so if a person's average daily sale is between
14 and 14.49, he gets 100%.
[....]
so basically each .5 increase in the figure will
increase the %age by 5.

Ostensibly, I think you want (A1 = daily sale):

=1 + int((A1-14)/0.5)*5%

formatted as Percentage.

But what if the daily sale is less than 14?

Perhaps you want one of the following:

' all sales <= 14 result 100% as well
=1 + int(max(0,A1-14)/0.5)*5%

' sales < 14 result in 0%
=if(A1<14, 0, 1 + int((A1-14)/0.5)*5%)

' reduce 100% by 5 pct pt for each 0.5 less than 14
=max(0, 1 + int((A2-14)/0.5)*5%)


----- original message -----

"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily
sale is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9 he
gets 105%. if it is 15.0-15.49, he gets 110% and so on. But I can not put
so many conditions in one formula...

so basically each .5 increase in the figure will increase the %age by 5.
But if it is less than .5 increase, it stays where it is. so if it is 14.6
or 14.7...14.9, it still remains 105% and the moment it reaches 15, the
%age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

Thanks Joe for the response. But it doesnt seem to working as I want it to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))

which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the sale value.
and the one you suggested is increasing 10% with each .1 increase in the
sale value.

What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.

Thanks again for your help with this.



"JoeU2004" wrote in message
...
"Gaurav" wrote:
so if a person's average daily sale is between
14 and 14.49, he gets 100%.
[....]
so basically each .5 increase in the figure will
increase the %age by 5.

Ostensibly, I think you want (A1 = daily sale):

=1 + int((A1-14)/0.5)*5%

formatted as Percentage.

But what if the daily sale is less than 14?

Perhaps you want one of the following:

' all sales <= 14 result 100% as well
=1 + int(max(0,A1-14)/0.5)*5%

' sales < 14 result in 0%
=if(A1<14, 0, 1 + int((A1-14)/0.5)*5%)

' reduce 100% by 5 pct pt for each 0.5 less than 14
=max(0, 1 + int((A2-14)/0.5)*5%)


----- original message -----

"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily
sale is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9 he
gets 105%. if it is 15.0-15.49, he gets 110% and so on. But I can not put
so many conditions in one formula...

so basically each .5 increase in the figure will increase the %age by 5.
But if it is less than .5 increase, it stays where it is. so if it is
14.6 or 14.7...14.9, it still remains 105% and the moment it reaches 15,
the %age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

Gaurav wrote:
Thanks Joe for the response. But it doesnt seem to working as I want it to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))



Can be simplified to this:

=IF(C5<11,0,IF(C5<12,0.4,IF(C5<13,0.6,IF(C5<14,0.8 ,IF(C5<14.5,1,1+(C5-14)*0.1)))))

When the first IF() fails, it confirms that C5=11, so no need to check that
again. Same goes for the rest, eliminating the need for AND().



which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the sale value.
and the one you suggested is increasing 10% with each .1 increase in the
sale value.

What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.



I think this will work for you:

=IF(C5<11,0,IF(C5<14.5,(INT(C5)-10)*0.2+0.2,(ROUNDDOWN(C5*2,0)/2-14)/10+1))



Thanks again for your help with this.

Thanks Glenn for simplifying the foumula. But I am still struggling to do
what I want to do :(


"Glenn" wrote in message
...
Gaurav wrote:
Thanks Joe for the response. But it doesnt seem to working as I want it
to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))



Can be simplified to this:

=IF(C5<11,0,IF(C5<12,0.4,IF(C5<13,0.6,IF(C5<14,0.8 ,IF(C5<14.5,1,1+(C5-14)*0.1)))))

When the first IF() fails, it confirms that C5=11, so no need to check
that again. Same goes for the rest, eliminating the need for AND().



which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the sale
value. and the one you suggested is increasing 10% with each .1 increase
in the sale value.

What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.



I think this will work for you:

=IF(C5<11,0,IF(C5<14.5,(INT(C5)-10)*0.2+0.2,(ROUNDDOWN(C5*2,0)/2-14)/10+1))



Thanks again for your help with this.

Does this formula do what you want...

=IF(C4<11,0,IF(C4<14,0.4+0.2*INT(C4-11),1+(MROUND(C4-0.25,0.5)-14)/10))

--
Rick (MVP - Excel)


"Gaurav" wrote in message
...
Thanks Joe for the response. But it doesnt seem to working as I want it
to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))

which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the sale
value. and the one you suggested is increasing 10% with each .1 increase
in the sale value.

What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.

Thanks again for your help with this.



"JoeU2004" wrote in message
...
"Gaurav" wrote:
so if a person's average daily sale is between
14 and 14.49, he gets 100%.
[....]
so basically each .5 increase in the figure will
increase the %age by 5.

Ostensibly, I think you want (A1 = daily sale):

=1 + int((A1-14)/0.5)*5%

formatted as Percentage.

But what if the daily sale is less than 14?

Perhaps you want one of the following:

' all sales <= 14 result 100% as well
=1 + int(max(0,A1-14)/0.5)*5%

' sales < 14 result in 0%
=if(A1<14, 0, 1 + int((A1-14)/0.5)*5%)

' reduce 100% by 5 pct pt for each 0.5 less than 14
=max(0, 1 + int((A2-14)/0.5)*5%)


----- original message -----

"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily
sale is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9
he gets 105%. if it is 15.0-15.49, he gets 110% and so on. But I can not
put so many conditions in one formula...

so basically each .5 increase in the figure will increase the %age by 5.
But if it is less than .5 increase, it stays where it is. so if it is
14.6 or 14.7...14.9, it still remains 105% and the moment it reaches 15,
the %age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

I forgot to mention... the MROUND function requires the Analysis ToolPak
add-in. If you get an error message, then click Tools/Add-Ins on Excel's
menu bar, put a check mark next to the Analysis ToolPak entry and click OK.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Does this formula do what you want...

=IF(C4<11,0,IF(C4<14,0.4+0.2*INT(C4-11),1+(MROUND(C4-0.25,0.5)-14)/10))

--
Rick (MVP - Excel)


"Gaurav" wrote in message
...
Thanks Joe for the response. But it doesnt seem to working as I want it
to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))

which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the sale
value. and the one you suggested is increasing 10% with each .1 increase
in the sale value.

What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.

Thanks again for your help with this.



"JoeU2004" wrote in message
...
"Gaurav" wrote:
so if a person's average daily sale is between
14 and 14.49, he gets 100%.
[....]
so basically each .5 increase in the figure will
increase the %age by 5.

Ostensibly, I think you want (A1 = daily sale):

=1 + int((A1-14)/0.5)*5%

formatted as Percentage.

But what if the daily sale is less than 14?

Perhaps you want one of the following:

' all sales <= 14 result 100% as well
=1 + int(max(0,A1-14)/0.5)*5%

' sales < 14 result in 0%
=if(A1<14, 0, 1 + int((A1-14)/0.5)*5%)

' reduce 100% by 5 pct pt for each 0.5 less than 14
=max(0, 1 + int((A2-14)/0.5)*5%)


----- original message -----

"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily
sale is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9
he gets 105%. if it is 15.0-15.49, he gets 110% and so on. But I can
not put so many conditions in one formula...

so basically each .5 increase in the figure will increase the %age by
5. But if it is less than .5 increase, it stays where it is. so if it
is 14.6 or 14.7...14.9, it still remains 105% and the moment it reaches
15, the %age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

Rick - YOU ROCK!!!

If you dont mind, will you please explain what does INT do here?

Thanks a TON for your help.




"Rick Rothstein" wrote in message
...
Does this formula do what you want...

=IF(C4<11,0,IF(C4<14,0.4+0.2*INT(C4-11),1+(MROUND(C4-0.25,0.5)-14)/10))

--
Rick (MVP - Excel)


"Gaurav" wrote in message
...
Thanks Joe for the response. But it doesnt seem to working as I want it
to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))

which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the sale
value. and the one you suggested is increasing 10% with each .1 increase
in the sale value.

What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.

Thanks again for your help with this.



"JoeU2004" wrote in message
...
"Gaurav" wrote:
so if a person's average daily sale is between
14 and 14.49, he gets 100%.
[....]
so basically each .5 increase in the figure will
increase the %age by 5.

Ostensibly, I think you want (A1 = daily sale):

=1 + int((A1-14)/0.5)*5%

formatted as Percentage.

But what if the daily sale is less than 14?

Perhaps you want one of the following:

' all sales <= 14 result 100% as well
=1 + int(max(0,A1-14)/0.5)*5%

' sales < 14 result in 0%
=if(A1<14, 0, 1 + int((A1-14)/0.5)*5%)

' reduce 100% by 5 pct pt for each 0.5 less than 14
=max(0, 1 + int((A2-14)/0.5)*5%)


----- original message -----

"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily
sale is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9
he gets 105%. if it is 15.0-15.49, he gets 110% and so on. But I can
not put so many conditions in one formula...

so basically each .5 increase in the figure will increase the %age by
5. But if it is less than .5 increase, it stays where it is. so if it
is 14.6 or 14.7...14.9, it still remains 105% and the moment it reaches
15, the %age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

INT is a standard Excel function.
Excel help will tell you what it does.
--
David Biddulph

Gaurav wrote:
Rick - YOU ROCK!!!

If you dont mind, will you please explain what does INT do here?

Thanks a TON for your help.




"Rick Rothstein" wrote in
message ...
Does this formula do what you want...

=IF(C4<11,0,IF(C4<14,0.4+0.2*INT(C4-11),1+(MROUND(C4-0.25,0.5)-14)/10))

--
Rick (MVP - Excel)


"Gaurav" wrote in message
...
Thanks Joe for the response. But it doesnt seem to working as I
want it to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))

which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the
sale value. and the one you suggested is increasing 10% with each
.1 increase in the sale value.

What I actually want is. a 5% increase with each .5 increase. so if
the increase in the sale value is .6 to .9, it would still increase
5%. Thanks again for your help with this.



"JoeU2004" wrote in message
...
"Gaurav" wrote:
so if a person's average daily sale is between
14 and 14.49, he gets 100%.
[....]
so basically each .5 increase in the figure will
increase the %age by 5.

Ostensibly, I think you want (A1 = daily sale):

=1 + int((A1-14)/0.5)*5%

formatted as Percentage.

But what if the daily sale is less than 14?

Perhaps you want one of the following:

' all sales <= 14 result 100% as well
=1 + int(max(0,A1-14)/0.5)*5%

' sales < 14 result in 0%
=if(A1<14, 0, 1 + int((A1-14)/0.5)*5%)

' reduce 100% by 5 pct pt for each 0.5 less than 14
=max(0, 1 + int((A2-14)/0.5)*5%)


----- original message -----

"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i
failed miserably. Anyway, here is the problem. so if a person's
average daily sale is between 14 and 14.49, he gets 100%. If it
is between 14.5-14.9 he gets 105%. if it is 15.0-15.49, he gets
110% and so on. But I can not put so many conditions in one
formula... so basically each .5 increase in the figure will increase
the
%age by 5. But if it is less than .5 increase, it stays where it is.
so
if it is 14.6 or 14.7...14.9, it still remains 105% and the
moment it reaches 15, the %age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

"Gaurav" wrote:
Thanks Joe for the response. But it doesnt seem to
working as I want it to.
[....]
=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

Your mistake is writing "/0.05" instead of "/0.5" as I wrote it. You want
the number of 0.5 increments, not 0.05 increments.

Simplifying, your formula becomes:

=if(C4 < 11, 0, if(C4 < 14, 0.4 + 0.2*int(C4-11), 1 +
int((C4-14)/0.5)*0.05))

formatted as Percentage.

Oh well, it appears that your moved on to other solutions. Personally, I
think you'd be much happier in the long-run sticking closer to your original
formula, which is more malleable.


----- original message -----

"Gaurav" wrote in message
...
Thanks Joe for the response. But it doesnt seem to working as I want it
to.

The daily sale is in C4 and here is my "previous" complete formula.

=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))

which I changed to the following after your suggestion.

=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))

The previous one was increasing 1% with every .1 increase in the sale
value. and the one you suggested is increasing 10% with each .1 increase
in the sale value.

What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.

Thanks again for your help with this.



"JoeU2004" wrote in message
...
"Gaurav" wrote:
so if a person's average daily sale is between
14 and 14.49, he gets 100%.
[....]
so basically each .5 increase in the figure will
increase the %age by 5.

Ostensibly, I think you want (A1 = daily sale):

=1 + int((A1-14)/0.5)*5%

formatted as Percentage.

But what if the daily sale is less than 14?

Perhaps you want one of the following:

' all sales <= 14 result 100% as well
=1 + int(max(0,A1-14)/0.5)*5%

' sales < 14 result in 0%
=if(A1<14, 0, 1 + int((A1-14)/0.5)*5%)

' reduce 100% by 5 pct pt for each 0.5 less than 14
=max(0, 1 + int((A2-14)/0.5)*5%)


----- original message -----

"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily
sale is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9
he gets 105%. if it is 15.0-15.49, he gets 110% and so on. But I can not
put so many conditions in one formula...

so basically each .5 increase in the figure will increase the %age by 5.
But if it is less than .5 increase, it stays where it is. so if it is
14.6 or 14.7...14.9, it still remains 105% and the moment it reaches 15,
the %age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks

Thanks everyone!!


"Gaurav" wrote in message
...
Hi,

I tried to explain the problem in the subject like but i know i failed
miserably. Anyway, here is the problem. so if a person's average daily
sale is between 14 and 14.49, he gets 100%. If it is between 14.5-14.9 he
gets 105%. if it is 15.0-15.49, he gets 110% and so on. But I can not put
so many conditions in one formula...

so basically each .5 increase in the figure will increase the %age by 5.
But if it is less than .5 increase, it stays where it is. so if it is 14.6
or 14.7...14.9, it still remains 105% and the moment it reaches 15, the
%age increase by 5 more and becomes 110%

I hope I was able to explain it properly. Please help.

Thanks