complicated excel formula

[sweetpeach]

Good evening!

Can you PLEASE help me with the formatting of my excel formula?

Basically, I am trying to recalculate my employee’s goals based on their
attendance. Their attendance is measured at 25%,50%, 75% or 100% based. I am
trying to program excel to read cell $c$6 and if that cell is 25%, use this
formula for collections:
=IF(AND(B61<=600.75),1,IF(AND(B61>=600.76,B61<=1200.75),2,
IF(AND(B61>=1200.76,B61<=1800.75),3, IF(AND(B61>=180.75,B61<=2400.75),4,
IF(AND(B61>=2400.76),5))))) and if cell $c$6 = 50%, use a formula to read
it’s variables. Can you please help? MS Online community will not allow me to
initiate a new question for some odd reason. Thanks



25% days wked
Goal 1
Goal 2
Goal 3
Goal 4
Goal 5

Collection
0-$600.75
$600.76-$1200.75
$1200.76-$1800.75


$1800.76-2400.75

$2400.76

Inbound Calls
<$250
$251-$275
$276-$300
$301-$325

$326

50% days wked






Collections
<$1201.50
$1201.51-$2401.50
$2401.51-$3601.50
$3601.51-$4801.50

$4801.51

Inbound Calls
<500
501-550
551-600
601-650

651

75% days worked






Collections
<$1802.25
$1802.26-$3602.25
$3602.26-$5402.25
$5402.26-$7202.25

$7202.26

Inbound calls
<750
751-825
826-899
900-975

976

100% days worked






Collections
<$2403
$2404-$4803
$4804-$7203
$7204-$9603

$9604

Inbound Calls
<999
1000-1099
1100-1199
1200-1299

 

[Max]

Try something like this:

=IF(C6=25%,IF(B61="","",IF(B61<=600.75,1,
IF(AND(B61>600.75,B61<=1200.75),2,
IF(AND(B61>1200.75,B61<=1800.75),3,
IF(AND(B61>1800.75,B61<=2400.75),4,
IF(B61>2400.75,5,)))))),"")

(cleaned up your limits & a typo, removed extraneous ANDs, & plugged in the
additional IF checks)

 

[sweetpeach]

THANK YOU..
I was able to utilize this format..You are a life saver..now I can get some
sleep!

 

[T. Valko]

What do the Inbound Calls tables have to do with it?

 

[Max]

welcome, sweetpeach.

 

[T. Valko]

No need for the ANDs:

=IF(C6=25%,IF(B61="","",IF(B61<=600.75,1,IF(B61<=1200.75,2,IF(B61<=1800.75,3,IF(B61<=2400.75,4,5))))),"")

A little bit shorter:

=IF(C6=25%,IF(B61="","",MATCH(B61,{0,600.76,1200.76,1800.76,2400.76})),"")

 

[T. Valko]

P.S

However, looking at their post, I think they should build a table that holds
all of the %s - 25, 50, 75, 100.

I don't see how the "Inbound Calls" are related, though.

 

[Max]

What do the Inbound Calls tables have to do with it?
Now that you say it, must admit I completely missed the portion of the orig.
post beyond the word "Thanks". For some strange reason. Maybe the word
simply triggers a natural termination of the post in the mind.

 

[T. Valko]

I'm reading the posts through OE. I always (for some strange reason!) look a
the size of the window scroll bar. If the scroll bar is long that means the
post is short. If the scroll bar is short that means the post is long.

I know, I'm strange! <bg>