how do i write an if then or formula where the value being "tested"is a RANGE of numbers??

[amber12177]

=IF(A4 is equal to or falls within the range of 6 to 7.75 then subtract .5) and (if A4 is equal to or falls within the range of 8 to 9.75 then subtract . 75) and if A4>=10, A4-1, but if A4 is less than 6 then it remains A4)



what i have is .......


IF(A4=[6;7.75],A4-.5,=IF(A4=[8;9.75],A4-.75,=IF(A4>9.75,A4-1,A4)))


........obviously wrong, as it doesn't work.......

suggestions??

 

[Claus Busch]

Hi Amber,

Am Sun, 27 Jul 2014 00:31:29 -0700 (PDT) schrieb (e-mail address removed):

=IF(A4 is equal to or falls within the range of 6 to 7.75 then subtract .5) and (if A4 is equal to or falls within the range of 8 to 9.75 then subtract . 75) and if A4>=10, A4-1, but if A4 is less than 6 then it remains A4)

try:
=A4-VLOOKUP(A4,{0,0;6,0.5;8,0.75;10,1},2,1)

possibly you have to modify the separators


Regards
Claus B.

 

[amber12177]

it doesn't work.....

 

[Claus Busch]

Hi Ron,

Am Sun, 27 Jul 2014 07:34:41 -0400 schrieb Ron Rosenfeld:

With regard to your current version of this question, note that the values from 7.75-8 and from 9.75-10 are undefined.

you are right. I didn't read carefully.

@Amber:
If my first answer is wrong then try:
=A4-0.5*AND(A4>=6,A4<=7.75)-0.75*AND(A4>=8,A4<=9.75)-1*(A4>=10)



Regards
Claus B.

 

[amber12177]

Ron, u asked me what was wrong w/ Isabelle's answer, I replied it doesn't work (meaning Isabelle's answer)....

what do you mean the values are undefined? my values (time) are in 15 min increments if an employee works at least 6 hrs but not more than 7.75 hrs they get a half hour lunch (.5), or if they work at least 8 hrs but not more than 9.75 hrs then they get a 45 min lunch (.75), or if they work more than9.75 hrs they get an hour lunch (1), but if they work less than 6 hrs theyget no lunch and the time remains its self.... I'm not sure how to state this more clearly.....

 

[amber12177]

Hi Claus,

I really appreciate you trying to help me, I've been struggling w/ this formula for weeks now.....

when I plug in your formula

=A4-0.5*AND(A4>=6,A4<=7.75)-0.75*AND(A4>=8,A4<=9.75)-1*(A4>=10)

it tells me that the formula contains an error, I will admit I'm a little over my head here, I can't figure out what "error" it's talking about....

the formula I've been trying to work with is as follows.....

=IF(A4=[6;7.75],A4-.5,=IF(A4=[8;9.75],A4-.75,=IF(A4>9.75,A4-1,A4)))

but when i enter this formula ms excel pop up says "The name you entered is not valid....

any ideas??

 

[Claus Busch]

Hi Amber,

Am Sun, 27 Jul 2014 09:15:13 -0700 (PDT) schrieb (e-mail address removed):

=A4-0.5*AND(A4>=6,A4<=7.75)-0.75*AND(A4>=8,A4<=9.75)-1*(A4>=10)

it tells me that the formula contains an error, I will admit I'm a little over my head here, I can't figure out what "error" it's talking about....

please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "TimeA"


Regards
Claus B.

 

[Vidcapper]

Actually, both of Claud's formulas work here.

I am curious about the algorithm though.

It seems that a person who works "up to the margin" may get as much
or even more than someone who works to the next level.

In other words, applying your "subtractions", someone who works 5.75
will have more time than someone who works 6.00. And at the other
margins (7.75-8.00, 9.75-10.00) there is no increased time credit
even though they worked 15 minutes longer. So it seems as if the
incentive for the employee is to decrease the amount worked.


Times Actual Adjusted 5.75 5.75 6.00 5.50 7.75 7.25 8.00 7.25 9.75
9.00 10.00 9.00

Better hope the boss doesn't spot that, then...