Hello Team,
Need help in creating a formula that will summarized or get the overall results base on 3 level category. "Low" "Medium" and "High"
Column A3:A14 are my process and column B3:B14 are its complexity. On column B15 I want to show the overall complexity of my business only answerable by only Low, Med and High.
I tried several formula but my returning values is not what Im expecting.
Appreciate all the help I can get.
Many thanks
Why not assign values of 1, 2, 3, rather than Low, Medium and High? Then you could get the Average which, in the example would be 1.2 ... and maybe use Round to eliminate the decimal places.
So, overall, I would expect the complexity of your business to be Low.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
This should work
=LOOKUP(SUMPRODUCT(MATCH(B3:B14,{"Low","Medium","High"},0)),{0,18,30},{"Low","Medium","High"})
Low up to 17
and Medum up to 30 points based on 12 processes.
You can make this more dynamic but this will required different formula.
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Hi Robert, may I see what the formula looks like if I to make it more dynamic? Many thanks
Would you still have 3 levels or more?
I was thinking what if each items in column A has correspoding weights e.g. A3 is 10% A4 is 8% and a total of 100% for all 12 items. And complexity (Low, Med and High) would have impact on the overall results. Say the rest of the items are all low except for item on A14 which has a weight of 50% and its complexity is High, then I should get an overall complexity of Medium. Can this work? Thanks again
We need to establish first what LOW means(in %), the same for Medium.
If a High is 50% then low is 4.54%?
What about Medium in this case?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Ex-xcel 
Register To Reply
Bookmarks