Calculate quota

[jw01]

hello

im having some difficulty placing some logic into a formula i have....the formula i have works exactly how i want to, but i cant seem add a futher logic to it

-basically, im calculating target quota for sales people based on their "hire date" in retrospect to the "currently month...year-to-date" the company is in...

formula: - for the purpose below (the report is for month of 31-may-2010

person below was hired: 10-may-2010; thus he's worked a total of 2.84 weeks by the of end 31-may-2010....

=if($C$7<AS$7,"",IF(S14>=0,(AS$9*AS$10),""))

C7 = current month end date (i.e. could be dec 31, 2010)
AS7 = particular month end date (i.e. if we are in may, then may 31, 2010)
S14 = # of weeks person with company in weeks (i.e. 2.84 weeks @ end of may 31)
AS 9 = quota for month $100
AS10 = # of weeks in month 4

based on this, the end result should give me (2.84 weeks)*($100) = $284 month quota


BUT my formula is giving me $400 bc i cant add logic for it to use 2.84 weeks instead of full 4 weeks (this particular person joined company in the middle of the month, so he has worked 2.84 weeks instead of a full month at 4 weeks)

....a person can start at any time frame in a month, near the beginning, middle of end, so i need it to check to see if he's worked less than the 4 or 5 weeks at the end of each month, use the # of weeks they have worked * $100


i hope its some what clear what im asking for, or looking to go with this....help is greatly appreciated guys, thxs

[arthurbr]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[KiPA]

It wasn't so clear after all.

How is from 10-may to 31-may 2,84 weeks? I get exactly 3 weeks.

# of weeks in month 4

What?

Also: all information besides hire date, particular month end date and quota for month is irrelevant, right?

[jw01]

hey guys

i have attached the workbook

please scroll to the right as i have highlighted the cell in "red"...that should be using the 2.84 weeks

-also, the # of weeks is calculated based on our calender year calculations.....

also please note the 'tenure calculation" from cells O-Z is from original hire date...it's not for that particular year
i.e. 1 Jan - 30-Jan is 4 weeks
ie. 1 feb - 28 feb is 4 weeks....so 8 weeks in total etc

[email protected]

[jw01]

any input guys?.....please and thxs

[KiPA]

It's still quite a mess and it was hard to get any logic out of the formula. Especially when you did not use the same example in your first post which is in the example file.

The first ifs check whether there is something in C column and if you're currently past the current month end date, right? But: all it does after that is that it simply checks whether an employee has positive working weeks in his/her hiring month and after that it multiplies always that 4 or 5 week with in this case that $200.

So: this might be something you were looking for:

=IF(C14>0;1;0)*IF(C7>AQ7;1;0)*IF(Q14>0;Q14*AQ9;AQ10*AQ9)

I didn't put ifs inside each other because it's way much easier to see the logic when they are one after another (consider * as "and"). I also didn't put any locks on and previously blank-returned results are zeroes. I'm sure you'll figure these out.

Did any good? Did I miss something?

P.S. Check "Want to get your question answered quickly?" at rules. Might be helpful next time