I'm working on a spreadsheet to rank stores based on how they perform in certain metrics. These metrics are weighted, and occasionally a metric for a store will get waived. I'm having trouble figuring out how to handle this without making a custom formula for each occurrence.
Attached is an example spreadsheet that should help explain my problem.
Any help would be appreciated.
Last edited by Will087; 10-22-2009 at 10:58 AM. Reason: Edited title to mark as awesomely solved.
Will Waived only appear in column B?
Where do you get 130% of Boston's Score? And is Score the addition of the numbers excluding Waived?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
No, it could appear in B,C or D.Will Waived only appear in column B?
The idea is to not penalize the "store ranking" of the store that had a metric waived.Where do you get 130% of Boston's Score?
Yes, but the idea is work around a 100 point scale.And is Score the addition of the numbers excluding Waived?
Not sure, but try:
=IF(COUNTIF(B3:D3,"Waived"),SUM(B3:D3)*(SUMIF(B3:D3,"Waived",$B$8:$D$8)+1),SUMPRODUCT((B3:D3)*(B$8:D $8)))
copied down.
Is that what you were looking for?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Okay, I got my math abit wrong and I did a bad job of explaining, take a look at the "original solution" column I added. I think that will help you get the jyst of what I was going for.
Thanks for your help.
Based on what i think you're trying to do in the "original solution" column.
I think that may have it, I'll figure out for sure sometime tomorrow. Thanks!
Confirmed, that was exactly what I needed. Thanks much NBVC and WHER.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks