• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating formula with weighted evaluations

H

hmoorex3

New Member
This is a new post off an existing one labled "how do you make multiple evaluations in a formula" from 2 days ago. I've determined that my real issue is that I'm getting inflated numbers when the formulas on rows 47-50 are evaluated. In a perfect world if there were 10 cities added or under contract, the distribution would be 3/3/3/1. But if there are only say 3 cities the formula would return a much higher number than I want. I would want to weight more towards row 74, then 75, then 76 and lastly toward 77. So for 3 cities I would want it to be 2*R74 and 1*R75 - none for R76 & R77. If there were 8 cities it would be something like 3*R74, 3*R75, 1*R76 & 1*R77. 17 cities might be 8*R74, 6*R75, 2*R76 & 1*R77. At this point it's basically arbitrary until there's empirical data to provide direction. I want to also have the capability to easily tweak the ratios if possible.


For commissions, the formula needs to evaluate the number of cities under contract for that month. For bonus', it needs to be only for number of cities ADDED that month.


Finally I have 5 similar sheets (different years) that I want to be able to make changes simultaneously to. I tried control and selecting the sheets but must not be doing it correctly as the changes wouldn't take. Any help there would be greatly appreciated.

Thank you.

https://dl.dropbox.com/u/25491363/Adaptive%20spreadsheet_r2.xlsx
 
Hmoorex3


You will have to replace the values 0.3, 0.1 etc with a lookup


I'd suggest setti8ng up a table of Cities vs Factors 1 to 4

Populate the table

then adjust the formulas


refer: https://www.dropbox.com/s/drps167bsgxsfxn/Adaptive%20spreadsheet_r2_Hui.xlsx

I have only adjusted the Row in Blue
 
I'm sorry, I'm kind of thick - I tried to understand what was going on by following the evaluation but got lost so a few questions if you please....


1)the formula you modified - what role does C2 play in the evaluation? Would I leave it and replace C6 with C7 for the hosting values?


2)what does the 2, 3, 4 and 5 after the table parameters signify?


3) why did you stop at 30 cities in the table?


4) how did you choose the form values? How do I know what to populate the rest of the table with?

Thank you Hui.
 
Hui,


I guess your feelings got hurt since you whined and cried about my post http://chandoo.org/forums/topic/clueless-and-stressing-how-do-you-make-multiple-evaluations-in-one-formula.

I posted the question about you wanting to help because it had been almost a week since I had last heard from anyone and I genuinely wanted to know if I should go to another resource. I apologized (even though I don't believe I did anything wrong)and posed some additional questions. Guess what? No one ever responded. Then I started this thread and as you can see - no response and it's been six days and yet I see you responding to several other new posts multiple times a day.


Get over yourself. And btw, the solution you gave me was so ridiculously overengineered & complicated a local college professor couldn't easily figure it out. I ended up coming up with a solution myself and if you had paid attention to your audience you would have known I'm a novice user.
 
Hi hmmorex3,


I suggest you to focus more on what do you want to get from this forum and out of your data and keep in touch by replying promptly. Sometimes and more often it is easier for other, and many times i have found it way too useful, if OP can post a clear cut requirement, all at once. If you post your requirements in parts, it is actually wasting time of others.


With due Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top