RAJESH1960
Active Member
 Joined
 Mar 26, 2020
 Messages
 449
 Office Version

 2019
 Platform

 Windows
Hello experts, I am posting the image of the file with full details. If it is possible to enter a formula as discussed in the sheet.....
Query Multiple IF's.xlsx  

A  B  C  D  E  F  G  H  J  L  N  P  Q  R  S  T  U  V  W  X  
1  STATIC VALUE  VARIABLE COLUMNS  COLUMNS WHERE THE FORMULA TO BE ENTERED  VARIABLE COLUMNS  
2  GRAND TOTAL  A TOTAL  B TOTAL  A  B  C  D  A  B  C  D  R/O  Diff  
3  Rows for reference only  x  x  5  12  18  28  2.5  6  9  14  
4  105  100  100  2.5  5  0  ROW 4  ROW 5  ROW 6  ROW 7  
5  112  100  100  6  12  0  IF B4="",""  IF B5="",""  IF B6="",""  IF B7="",""  
6  118  100  100  9  18  0  IF J4,L4 AND N4=""  IF H5, L5 AND N5=""  IF H6,J6 AND N6=""  IFJ7, J7 AND L7=""  
7  128  100  100  14  28  0  THEN E4,F4 AND G4=""  THEN D5,F5 AND G5=""  THEN D6, E6 AND G6=""  THEN D7,E7 AND F7=""  
8  217  200  100  100  2.5  6  17  0  AND D4=B4  AND E5=N5/6*100  AND F6=P6/9*100  AND G7=R7/14*100  
9  230  200  100  100  6  9  30  0  
10  246  200  100  100  9  14  46  0  ROW 8  ROW 9  ROW 10  ROW 11  
11  223  200  100  100  2.5  9  23  0  IF B8="",""  IF B9="",""  IF B10="",""  IF B11="",""  
12  233  200  100  100  2.5  14  33  0  IF L8 AND N8=""  IF H9 AND N9=""  IF H10 AND J10=""  IF J11 AND N11=""  
13  335  300  100  100  100  2.5  6  9  35  0  THEN F8 AND G8 =""  THEN D9 AND G9=""  THEN D10 AND E10=""  THEN E11 AND G11 =""  
14  358  300  100  100  100  6  9  14  58  0  AND D8=H8/2.5*100  AND E9=J9/6*100  AND F10=L10/9*100  AND D11=H11/2.5*100  
15  351  300  100  100  100  2.5  9  14  51  0  AND  AND  AND  AND  
16  345  300  100  100  100  2.5  6  14  45  0  F8=B8D8  G8=B9E9  G10=B10F10  F11=B11D11  
17  463  400  100  100  100  100  2.5  6  9  14  63  0  
18  240  200  100  100  6  14  40  0  ROW 12  ROW 13  ROW 14  ROW 15  
19  IF B12="",""  IF B13="",""  IF B14="",""  IF B15="",""  
20  I WILL TRY TO EXPLAIN MY PROBLEM AS SIMPLE AS POSSIBLE  IF J12 AND L12=""  IF N13=""  IF H14=""  IF J15=""  
21  If you change the amount in column B the difference amount in column Y should be zero only  THEN E12 AND F12 =""  THAN G13=""  THAN D14=""  THAN E15=""  
22  I have given different formulas manually in columns D to K  AND D12=H12/2.5*100  THEN D13=H13/2.5*100  AND E14=J14/6*100  AND D15=H15/2.5*100  
23  There can be 15 different calculations for each amount entered in Column B  AND  AND  AND  AND  
24  The number of entries are in 1000's. I have been calculating it manually for each row using sort and filter options  G12=B12=D12  E13=J13/6*100  F14=L14/9*100  F15=L15/9*100  
25  I know it is possible to make it easy by giving a formula in the Row 4 from D4:K4 in each column and copy the formula till the last entry  AND  AND  AND  
26  Due to lack of knowledge of multiple "IF", "AND" & "OR", I am finding it difficult to solve this. I would like to know and understand  F13=B13H13J13  G14=B14J14L14  G15=B15D15L15  
27  Since the last few weeks I have been trying but not able to solve it. I would really appreciate any help in solving this.  
28  Why don't you experts give it a try. If the solution is solved it will save me hours of work  ROW 16  ROW 17  ROW 18  
29  Please note that the amounts in columns H:P are also variables like in column B  IF B16="",""  IF B17="",""  IF B18="",""  
30  Column C is an extension of my project and it will require to change only the references once I find a solution  IF J16=""  IF H17, J17,L17 AND N17<>""  IF H18 AND L18 =""  
31  How it works  THEN F16=""  THEN D17=H16/2.5*100  THEN D18="", F18=""  
32  In columns H,J,L and N if there is amount in one cell only then it takes the amount from column B  AND D16=H16/2.5*100  AND E17=J17/6*100  AND E18=J18/6*100  
33  if there are amounts in any 2 cells in H, J, L and N then the first cell in D, E, F and G should be calculated from the corresponding column  AND  AND F17=L17/9*100  AND G18=B18E18  
34  and the second cell amount should be taken after deducting the total amount from the first calculated amount  F16= L16/9*100  AND G17=B17D17E17F17  
35  if there are amounts in 3 cells the again the first cell amount should be calculated from the corresponding column  AND  
36  the second amount also to be calculated from the corresponding column  G16=B16D16F16  
37  and then in the third cell it should deduct the total amount from the first and second calculation amounts  
38  if there are amounts in all 4 cells then the first cell amount should be calculated from the corresponding column  
39  the second cell amount also to be calculated from the corresponding column  
40  the third cell amount also to be calculated from the corresponding column  
41  and the fourth cell it should deduct the total amount from the first, second and third calculated amounts  
42  
43  
44  
45  
QUERY 
Cell Formulas  

Range  Formula  
D4  D4  =B4 
P4:P18  P4  =B4+C4SUM(D4:O4) 
Q4:Q18  Q4  =A4SUM(D4:O4) 
E5  E5  =B5 
F6  F6  =B6 
G7  G7  =B7 
D8,D15:D17,D11:D13  D8  =H8/2.5*100 
E8  E8  =B8D8 
E9,E16:E17,E13:E14  E9  =J9/6*100 
G10  G10  =B10F10 
F9  F9  =B9E9 
F10,F17,F14:F15  F10  =L10/9*100 
F11  F11  =B11D11 
G12  G12  =B12D12 
F13  F13  =B13D13E13 
G14  G14  =B14E14F14 
G15  G15  =B15D15F15 
G16  G16  =B16D16E16 
A4:A17  A4  =SUM(C4:O4) 
G17  G17  =B17D17E17F17 