Formula Help

[NL]

Hi all I'm a beginner user, and I'm trying to implement the following formula, please consider that all cells are number =IF(OR(D2=4;E2=1;F2=1;G2=1;H2=1;I2);"Right";IF(OR(J2=1;K2=1;L2=1;M2=1;N2=1;O2=1;P2=1;Q2=1;R2=1;S2=1);"wrong";SUM(T2:Z2))) Can someone please help me? Thank you in advance

[Anonymous]

Hi, 

Could you try and explain what you're trying to do? Instead of just 'fixing' your code we might find something that is a lot more fit for purpose.

[NL]

Sure 

I'm trying to define a scoring system 

My input data ( table form Stock to Proxy 23) is a table 24X1M. For each row, I have to define the score in the column "final results" with 3 check

1. If the value of Stock is  4 in Prox1 or 1 in Proxy__02 or 1 in Proxy_03 than the result should be “high” in the final score; if false
2. Check the value in Proxy_07 to Proxy_16 and if is 1 in Proxy_07 or 1 in Proxy_08 or …. The result should be “low” in the final score; if false
3. Do the sum between Proxy_17 to Proxy_23 and if the value is lower than 2 than the results should be “Not”; if the value is between 2-4 “medium”, otherwise “low” in the final score

=IF(OR(B3=4;C3=1;D3=1;E3=1;F3=1;G3);"high";IF(OR(H3=1;I3=1;J3=1;K3=1;L3=1;M3=1;N3=1;O3=1;P3=1;Q3=1);"low";SUM(R3:X3)))

if one of the cells indicated in the if formula have the selected value ( 4,1,1,..) the results should be high or low base on formula otherwise the results should be the sum of the cell between Proxy_17 to Proxy_23

INPUT
STOCK	Proxy _01	Proxy _02	Proxy _03	Proxy _04	Proxy _05	Proxy _06	Proxy _07	Proxy _08	Proxy _09	Proxy _10	Proxy _11	Proxy _12	Proxy _13	Proxy _14	Proxy _15	Proxy _16	Proxy _17	Proxy _18	Proxy _19	Proxy _20	Proxy _21	Proxy _22	Proxy _23	Final score
A	1	1	0	0	0	0	2	0	0	0	0	0	0	0	0	0	1	1	0	0	0	0	0	high
B	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	0	1	0	0	0	3
C	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	-1	1	0,5	0,6	0	high

where 

B3 =1 ( Proxy_01); C3=1 (Proxy_02) ...

[Anonymous]

Hi @NL ,

I think you can do unpivot columns function on your proxy fields, then you can write if statement condition more simply:

Unpivot columns (Power Query)

Regards,

Xiaoxin Sheng