Need to assign points to certain words (criteria)

[ExcelNewbie350]

Hi all,

I have some issues finding the correct formulas for the following problem.

Let's say a number of staff members are working on incoming cases. Management wants to establish which of these cases in the backlogs are most important to work with, so the following points system is used:

Priority of the case: High = 3 points, Medium = 2 points, Low = 1 point
Severity of the case: Severity 1 = 3 points, Severity 2 = 2 points, Severity 3 = 1 point
Case open (days): For every 15 days a case has been opened the value is 0.25 points.

So these are my 3 criteria.

Now I am looking at a spread sheet which will contain columns corresponding with the criteria, like Priority, Severity and how many days a case has been opened for.

How will I end up with a single number (Priority points + Severity points + points for the length of case being open)?

I hope this makes sense! I do understand what it's good for but have no idea how to get there!

Any help would be much appreciated!

Many thanks

[Oscar Martin]

Could you be more specific about how you want your spreadsheet to function? For example do you want a set of formulas that will return the three criterions of any given case when a case ref number is entered into a cell? Or do you want a formula that will extract only those cases that come under a specific Severity/Priority/initiation date?

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ExcelNewbie350]

Could you be more specific about how you want your spreadsheet to function? For example do you want a set of formulas that will return the three criterions of any given case when a case ref number is entered into a cell? Or do you want a formula that will extract only those cases that come under a specific Severity/Priority/initiation date?

Hi Oscar, thank you for replying. I will attach a mock sheet shortly to explain what I'm trying to do. I hope this will clear things up.

[ExcelNewbie350]

Hi Davesexcel,

Thanks a lot for your reply. Please find attached the mock sheet. I hope it's clear enough?

Best wishes!

Mock Sheet.xlsx

[Oscar Martin]

If you just want to add a series of values together to get one figure you can just use a simple SUM formula. But you would be adding a set of values which are based on a different unit of measurement. It's a kind of apples + oranges situation.

[ExcelNewbie350]

Hmmm, Oscar, I'm not sure if it's just sums... have you had a look at the mock sheet I posted? I attached it again...

[Oscar Martin]

Okay, I've used a SUM formula to give you a single figure. Let me know if this is what you were looking for.

Cheers,

Oscar.

[ExcelNewbie350]

Hi Oscar,

Thank you for sending me a SUM formula. Unfortunately this is not what I'm looking for. I think it will be a really long IF formula. It is right that we will end up with a sum of the 3 components. But I will need formulas for each of the components first.... Are you with me?

[Oscar Martin]

Okay, you will see two cells highlighted in green. You can change the Priority Cell by typing 'HIGH', 'MEDIUM', or 'LOW' into it and the green cell below it will deliver 3,2, or 1 respectively. It is a relatively basic IF formula. If that's what you were after I will complete the three conditions for you using the same formula. I just wanted to make sure we were on the same page first.

(see attached)

Oscar.

[ExcelNewbie350]

Thank you for your help on this. I'm still not quite sure if your formula will get us to the end numbers which are listed in the 'Weight' section. I need to be able to get to these numbers through formulas. I didn't quite understand why you used B2? Because B2 can always change whereas G2 - G6 wouldn't change... don't we have to work with those fields to make the formula work? Something like, if the B column shows 'High' then it should be '3' and both will be taken from G and H column? Does that make sense?

Thanks again,
Alex

[Oscar Martin]

Okay, I've altered the formulas so that any change to the values in the Priority and Severity columns to the left, will alter the valuesin the Priority Rating and Severity Rating columns to the right.

If this is closer to what you had in mind let me know, and I'll start working on the Day Rating.

Oscar.