Calculate a value based on several conditions, dont think "if" function works

You could have a small table somewhere on your sheet for the quality and weighting factor, like this:

Good ........ 1.2
Medium .... 1
Poor ......... 0.8

then you could use a VLOOKUP function to get the appropriate factor.

It would help if you attached a sample Excel workbook, by following the guidelines given in the yellow banner at the top of the screen.

Hope this helps.

Pete

Sorry guys, i'm new here! This is an example of a the sheet i'm working on.

Thank you again

If you have your table of quality and Factor in cell H2:I5 (to allow for a Very Good entry), then you can use this formula in F2:

=E2*VLOOKUP(C2,$H$2:$I$5,2,0)

Copy down as required.

Note that you could use COUNTIF in column E rather than hard-code the number of stores, i.e. in E2:

=D2/COUNTIF(B:B,B2)

Hope this helps.

Pete

That's my problem exactly. The factors will change based on the different stores present. Because if i have a micromarket with a Good a Medium and a Poor. For that scenario the factors are 1.25, 1 and .75 respectively. But in a scenario with a Very Good, a Good, a Medium and Poor the factors would be 1.5, 1.25, .75 and .5 respectively. In a scenario with only Medium and Poor the factors should be like 1.10 for Medium and 0.90 for Poor.

Sorry for the mess don't know if i'm explaining it well..

There are a number of different combinations that you could "theoretically" have, e.g. Good and Poor stores only, or Very Good with Medium, and so on. Also, does the number of stores of each type affect the factors for a particular market, e.g. if you have a market with 1 Good, 5 Medium and 1 Poor (7 in total), would the factors be the same for a market with 3 Good, 1 Medium and 3 Poor?

You need to explain your variables more clearly.

Pete

Using V for Very good, G for Good, and so on, you can have these variations of stores making up the micro markets:

V G M P
V M P
V G P
V G M
G M P
V G
V M
V P
G M
G P
M P
V
P
M
G

Of course, some of these may not exist, e.g. those with only a single type.

Instead of the simple two-column table that I suggested earlier, you could now compose a table with these market types in the first column, then 4 other columns for the Quality, and then record the factors in the appropriate column for each market type. Then a modified VLOOKUP (or INDEX/MATCH) formula could get you the appropriate factor, assuming you can classify the Market type as one of the above.

Hope this helps.

Pete

I guess this is the best way to simplify things. Maybe i was over complicating with the several different scenarios and the different factors for each scenario. This seems to me to be the best solution for the problem. Thank you very much