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

user_19348388 0
Viewed 117

enter image description here

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:

  1. 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:

  1. 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!

excel
1 Answers

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")))

EDIT: comment's request

We need to dynamically change the reference to the (appropriate cell in the) row 729. To achieve this, we will use this formula:

INDIRECT( hardcoded column & ROW( top-left cell of the vehicle list ) - 1 + MATCH( cell with the kind of the given vehicle , vehicle list , 0 ))

INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))

which we will adapt for each cell needed:

Previous reference New formula
AD$729 INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AE$729 INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AF$729 INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AG$729 INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AH$729 INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AI$729 INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))

so we will be able to substitute them accordingly and obtain this:

=IF(OR(AD2="N/A",AD2=0),0,AD2*INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))

Second edit

I've re-written the formula tuning it for the new request. Try it:

=IF(OR(AL2="N/A",AL2=0),0,AL2*INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AM2="N/A",AM2=0),0,AM2*INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AN2="N/A",AN2=0),0,AN2*INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AO2="N/A",AO2=0),0,AO2*INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AP2="N/A",AP2=0),0,AP2*INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))

Here a table to make the range change easier:

A B C D E F G
weight sheet Sheet1
weight row 729
weight column AL =LEFT(MID(CELL("address",OFFSET(INDIRECT(C3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(C3&"1"),0,1)),2,99))-1) =LEFT(MID(CELL("address",OFFSET(INDIRECT(D3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(D3&"1"),0,1)),2,99))-1) =LEFT(MID(CELL("address",OFFSET(INDIRECT(E3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(E3&"1"),0,1)),2,99))-1) =LEFT(MID(CELL("address",OFFSET(INDIRECT(F3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(F3&"1"),0,1)),2,99))-1)
value sheet Sheet1
value row 2
value column AL =LEFT(MID(CELL("address",OFFSET(INDIRECT(C6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(C6&"1"),0,1)),2,99))-1) =LEFT(MID(CELL("address",OFFSET(INDIRECT(D6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(D6&"1"),0,1)),2,99))-1) =LEFT(MID(CELL("address",OFFSET(INDIRECT(E6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(E6&"1"),0,1)),2,99))-1) =LEFT(MID(CELL("address",OFFSET(INDIRECT(F6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(F6&"1"),0,1)),2,99))-1)
case sheet Sheet1
case row 2
case column B
weight index address Sheet1!$AK$729:$AK$734
weight ="INDIRECT(""" & $C$1 &"!" & C3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))" ="INDIRECT(""" & $C$1 &"!" & D3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))" ="INDIRECT(""" & $C$1 &"!" & E3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))" ="INDIRECT(""" & $C$1 &"!" & F3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))" ="INDIRECT(""" & $C$1 &"!" & G3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))"
value =$C$4&"!" &C6&$C$5 =$C$4&"!" &D6&$C$5 =$C$4&"!" &E6&$C$5 =$C$4&"!" &F6&$C$5 =$C$4&"!" &G6&$C$5
weight * AND(value<>0, value<>"N/A") formula 1 =C13&"*"&"AND("&C14&"<>0,"&C14&"<>""N/A"")" =D13&"*"&"AND("&D14&"<>0,"&D14&"<>""N/A"")" =E13&"*"&"AND("&E14&"<>0,"&E14&"<>""N/A"")" =F13&"*"&"AND("&F14&"<>0,"&F14&"<>""N/A"")" =G13&"*"&"AND("&G14&"<>0,"&G14&"<>""N/A"")"
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 formula 2 =C16&"+"&D16&"+"&E16&"+"&F16&"+"&G16
value * weight / previous formula formula 3 =C14&"*"&C13&"/"&"("&$C17&")" =D14&"*"&D13&"/"&"("&$C17&")" =E14&"*"&E13&"/"&"("&$C17&")" =F14&"*"&F13&"/"&"("&$C17&")" =G14&"*"&G13&"/"&"("&$C17&")"
IF(OR(value = "N/A", value = 0), 0, previous formula) formula 4 ="IF(OR("&C14&"=""N/A"","&C14&"=0),0,"&C18&")" ="IF(OR("&D14&"=""N/A"","&D14&"=0),0,"&D18&")" ="IF(OR("&E14&"=""N/A"","&E14&"=0),0,"&E18&")" ="IF(OR("&F14&"=""N/A"","&F14&"=0),0,"&F18&")" ="IF(OR("&G14&"=""N/A"","&G14&"=0),0,"&G18&")"
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 formula 5 =C19&"+"&D19&"+"&E19&"+"&F19&"+"&G19

Crazy formulas calls for crazy way to edit.

user_87802580
Related