Excel weighting

[average.1]

Hi all,

Looking for some help, as I am struggling to get an answer that works.

Example:
- I have a number of items listed (currently 70 line items) with a scoring system attached to each line (1.00-3.50)
- from these line items, certain items will be picked throughout the year and I need to create a measuring system to understand the risk associated with them.

- Anything between 1.00-2.00 will immediately raise a red flag and overrides all remaining rules.
- if I then split the remaining items in 0.50. If 2 items from 2.00-2.50 are picked then a red flag is raised. If 3 items from 2.50-3.00 then a red flag raised. If 4 items picked from 3.00-3.50 then a red flag raised.

- if the total number of items between 2.00-3.50 equates to 6 and the above flags aren’t raised, then a red flag is raised as the risk of these items is over then defined threshold.

How exactly would I build this? Getting confused and falling short somewhere!

[FDibbins]

Hi, welcome to the forum

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.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[GeoffW283]

Here is an attempt. Items are in col-A, scores are in col-B. Put a "y" in col-C for each picked item. I have dealt with each of your 5 conditions separately with formulas in D2:H2

Here is the formula in E2 which covers the condition: "If 2 items from 2.00-2.50 are picked then a red flag is raised"

Please Login or Register  to view this content.

Other formulas are similar.

Finally I2 is set to TRUE of any of the 5 conditions are true. I then used conditional formatting to make your red flag (cell I2) red.

See the attached workbook.

[FDibbins]

Here is an attempt...

Nice attempt

[GeoffW283]

Thanks Ford - and thanks for the rep!

[average.1]

Hi geoff,

Really like this, thank you!

Have played around, but seem to have made a mistake somewhere? Only generating false

[GeoffW283]

The problem is that all your values in column B are "text" - they need to be numbers!

One way to fix: select B2:B60,left-click on the yellow alert and select "convert to number"

In fact I have fixed it in a revised version of your workbook (attached).

[average.1]

Thanks, stupid mistake on my end!

Another question (trying to look at another option of grouping this), if I wanted to take an average score rather than an absolute number, how would I go about that?
i.e. between 2.51 - 3.00, if the average is greater than "X" value then a red flag (rather than 3 items being selected).

[GeoffW283]

Here is the formula that does what you want, I think. I have made your "X" = 2.7, and I'd have thought that the test would be less than "X" rather than greater than "X" ??

Please Login or Register  to view this content.

I have added this as condition-6 in the attached workbook.

[GeoffW283]

And thanks by the way for the reputation points

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.