I need to create a conditional formula in Excel with multiple conditions including relative and non--CodePudding

I have columns of criticality ratings in Columns AD through AI – each column has either a 1, 2, 3, 4, 5, N/A, or is blank. Column AJ is simply a placeholder column and Column AK is where I do my calculations to come up with a rebalanced criticality rating by multiplying each of the ratings from the first six columns by the weight of that rating area – in my instance, those weights are 30%, 20%, 20%, 10%, 15%, 5% across the six columns and these weights are listed in Row 729. I have two goals in processing the 727 rows of data:

If there is an ‘N/A’ or a blank in a column(s), take the percentages from those columns and reassign them equally to the other columns. So, in the first row in the image, the ratings are 5, BLANK, 2, 1, 1, 5 and the weights are 30%, 20%, 20%, 10%, 15%, 5%. If there was no reassignment, the formula would be (5 * 30%) (BLANK * 20%) (2 * 20%) (1 * 10%) (1 * 15%) (5 * 5%) = 1.5 0 .4 .1 .15 .25 = 2.4, which is the placeholder AJ column. My formula determines that the N/A and blank add up to columns representing 20% and there are 5 columns that have values in them, so I add 4% to each of those 5 columns, and my new math becomes (5 * 34%) (BLANK * 24%) (2 * 24%) (1 * 14%) (1 * 19%) (5 * 9%) = 1.7 0 .48 .14 .19 .45 = 2.96, which is the rebalanced AK column. Everything works great and the formula I use in Excel is:

=LET(total,SUMIF(AD2:AI2,"N/A",$AD$729:$AI$729) SUMIF(AD2:AI2,"",$AD$729:$AI$729),count,COUNTIFS(AD2:AI2,">=1",AD2:AI2,"<=5"),SUM(IFERROR((AD$729:AI$729 total/count)*AD2:AI2,0)))

My question is regarding the next goal:

If there is an ‘N/A’ or a blank in a column(s), take the percentages from those columns and reassign them based on their original weights to the other columns. So, in the first row in the image, the ratings are 5, BLANK, 2, 1, 1, 5 and the weights are 30%, 20%, 20%, 10%, 15%, 5%. How do I write the formula so that in this instance the 20% representing the BLANK in Column AF is added to AD and AF through AI based on the percentages of the columns in AD and AF through AI? I would still need to know ‘total’, which is the percentage to reassign, and in this case is 20%. But how do I look at the remaining columns, which in this case would be 30%, 20%, 10%, 15%, 5%, and reassign the 20% such that the new percentages would be the same relative to each other? Column AD starts out at 30%, Column AG starts out at 10%, and Column AH starts out at 15%, so AD needs to wind up with a weight three times as much as AG and twice as much as AH, and I need to account for the fact that any number of columns (up to all 6) can have blanks or N/A’s. In this instance the new weighted percentages would be 37.5%, 0%, 25%, 12.5%, 18.75%, 6.25%.

Thanks in advance!

CodePudding user response：

What about the following crazy formula?

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A")))

This one is tuned for the row 2. Weight are assumed to be stored as percentage (therefore if the cell format is set as general, 30% will be shown as 0,3).

The building up

First it return the base weight for the given cell if it's appropriate:

^{weight * AND(value<>0, value<>"N/A")}

AD$729*AND(AD2<>0,AD2<>"N/A")

which is repeated for each cell of the row:

^{previous formula for cell 1 previous formula for cell 2 previous formula for cell 3 previous formula for cell 4 previous formula for cell 5 previous formula for cell 6}

(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))

which is used to divide the product of value and weight:

^{value * weight / previous formula}

AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))

which is actually calulated only if necessary:

^{IF(OR(value = "N/A", value = 0), 0, previous formula)}

IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A")))

which is repeated for each cell of the row:

previous formula for cell 1 previous formula for cell 2 previous formula for cell 3 previous formula for cell 4 previous formula for cell 5 previous formula for cell 6

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A"))) IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A") AE$729*AND(AE2<>0,AE2<>"N/A") AF$729*AND(AF2<>0,AF2<>"N/A") AG$729*AND(AG2<>0,AG2<>"N/A") AH$729*AND(AH2<>0,AH2<>"N/A") AI$729*AND(AI2<>0,AI2<>"N/A")))