MULTIPLE IF AND OR FUNCTIONS

ROW 4

ROW 5

ROW 6

ROW 7

5

112

100

6

-12

IF B4="",""

IF B5="",""

IF B6="",""

IF B7="",""

6

118

100

9

-18

IF J4,L4 AND N4=""

IF H5, L5 AND N5=""

IF H6,J6 AND N6=""

IFJ7, J7 AND L7=""

7

128

100

14

-28

THEN E4,F4 AND G4=""

THEN D5,F5 AND G5=""

THEN D6, E6 AND G6=""

THEN D7,E7 AND F7=""

8

217

200

2.5

6

-17

AND D4=B4

AND E5=N5/6*100

AND F6=P6/9*100

AND G7=R7/14*100

9

230

200

6

9

10

200

9

14

-46

ROW 8

ROW 9

ROW 10

ROW 11

11

223

200

2.5

9

-23

IF B8="",""

IF B9="",""

IF B10="",""

IF B11="",""

12

233

200

2.5

14

-33

IF L8 AND N8=""

IF H9 AND N9=""

IF H10 AND J10=""

IF J11 AND N11=""

13

335

300

2.5

6

9

-35

THEN F8 AND G8 =""

THEN D9 AND G9=""

THEN D10 AND E10=""

THEN E11 AND G11 =""

14

358

300

6

9

14

-58

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

2.5

9

14

-51

AND

16

345

300

2.5

6

14

-45

F8=B8-D8

G8=B9-E9

G10=B10-F10

F11=B11-D11

17

463

400

2.5

6

9

14

-63

18

240

200

100

6

14

-40

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

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

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=B13-H13-J13

G14=B14-J14-L14

G15=B15-D15-L15

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=B18-E18

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=B17-D17-E17-F17

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=B16-D16-F16

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+C4-SUM(D4:O4)
Q4:Q18	Q4	=A4-SUM(D4:O4)
E5	E5	=B5
F6	F6	=B6
G7	G7	=B7
D8,D15:D17,D11:D13	D8	=H8/2.5*100
E8	E8	=B8-D8
E9,E16:E17,E13:E14	E9	=J9/6*100
G10	G10	=B10-F10
F9	F9	=B9-E9
F10,F17,F14:F15	F10	=L10/9*100
F11	F11	=B11-D11
G12	G12	=B12-D12
F13	F13	=B13-D13-E13
G14	G14	=B14-E14-F14
G15	G15	=B15-D15-F15
G16	G16	=B16-D16-E16
A4:A17	A4	=SUM(C4:O4)
G17	G17	=B17-D17-E17-F17

RAJESH1960 · Dec 9, 2020

Fluff said:
@RAJESH1960
In future please put your your question in the post, NOT in your spreadsheet as it makes things more difficult for any potential helpers.
Also please do not type everything in caps as it's considered shouting.
Thanks

Understood Fluff. Next time, I will keep in mind. No writing questions in the spread sheets and message not in caps.

RAJESH1960 · Dec 9, 2020

RAJESH1960 said:
Maabadi, I have posted again a different sheet. Please check

The columns g h i j in the original data are blank and are calculated manually.
If there is only one value in columns k m o q then in column g h i j the amount is taken directly from the subtotal
If there are more than one values in columns k m o q then
The first value is calculated by a formula as shown in column g h i j
And the last amount is taken after deducting the subtotal from the value of the calculated other cell
This way the gross total and subtotal and difference amounts will not change
To test the sheet, check the cells k m o q which have values in at least in 2 cells and change the amount in white cells to a lesser number. The sub total should not change
Example if you change the values in k6 from 29 to 19, then automatically the value in cell m6 should change and the sub total should be the same
That is why the second value of the cell in m6 should be calculated by deducting the value in f6 with k6
Same way where the white cells have values in more than one column then the last value should be calculated by deducting subtotal from the sum of values of calculated values
I want to replace the formula in g h i j with one single formula
Please check the formulas given in all the cells in columns g h i j. All are different which cannot be dragged down but will have to be done manually.

maabadi · Dec 10, 2020

This is for your 2nd Table. Insert at G2 and drag it right (to column J) & down.

Excel Formula:

=IF(AND(COLUMN()-COLUMN($F$2)=4,D2="",E2="",F2=""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/14*100),IF(COLUMN()-COLUMN($F$2)=4,$F2-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-3))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-3)))-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-1))),IF(AND(COLUMN()-COLUMN($F$2)=3,OR(D2<>"",E2<>"")),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-1))=0,$F2-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-1))),INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/9*100)),IF(AND(COLUMN()-COLUMN($F$2)=2,F2<>""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",IF(AND(INDIRECT(ADDRESS(ROW(),2*COLUMN()-1))=0,INDIRECT(ADDRESS(ROW(),2*COLUMN()+1))=0),$F2-$G2,INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/6*100)),IF(ROW()-ROW($F$2)<4,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",$F2),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/2.5*100))))))

RAJESH1960 · Dec 10, 2020

maabadi said:

This is for your 2nd Table. Insert at G2 and drag it right (to column J) & down.

Excel Formula:

=IF(AND(COLUMN()-COLUMN($F$2)=4,D2="",E2="",F2=""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/14*100),IF(COLUMN()-COLUMN($F$2)=4,$F2-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-3))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-3)))-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-1))),IF(AND(COLUMN()-COLUMN($F$2)=3,OR(D2<>"",E2<>"")),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-1))=0,$F2-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="",0,INDIRECT(ADDRESS(ROW(),COLUMN()-1))),INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/9*100)),IF(AND(COLUMN()-COLUMN($F$2)=2,F2<>""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",IF(AND(INDIRECT(ADDRESS(ROW(),2*COLUMN()-1))=0,INDIRECT(ADDRESS(ROW(),2*COLUMN()+1))=0),$F2-$G2,INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/6*100)),IF(ROW()-ROW($F$2)<4,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",$F2),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))=0,"",INDIRECT(ADDRESS(ROW(),2*COLUMN()-3))/2.5*100))))))

Maabadi, OMG!! You really worked this. Thanks a lot man, for your effort to solve it. But the total's change in cells G17, H17, I17 and J17 when I copied your formula and dragged it down, and the amounts too don't tally in columns H I and J. Only G is perfect. The columns H I AND J need some corrections. I hope you have the sheet with you. Then too I am posting the sheet after the formula is pasted.

new query mr.excel.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Date

Particulars

Vch No.

xyz

GROSS AMOUNT

SUB TOTAL A

5% A

12% A

18% A

28% A

VARIABLES 2.5%

VARIABLES 6%

VARIABLES 9%

VARIABLES 14%

Round Off

Diff

2

01-11-2020

January

1

1930.00

1840.00

46.00

-2.00

3

02-11-2020

February

2

3528.00

3150.00

189.00

4

03-11-2020

March

3

1311.00

2222.22

200.00

100.00

-1211.22

5

04-11-2020

April

4

1033.00

807.14

113.00

-0.14

6

05-11-2020

May

5

3626.00

3310.00

1160.00

2150.00

29.00

129.00

7

06-11-2020

June

6

5560.00

5191.43

4720.00

471.43

118.00

66.00

0.57

8

07-11-2020

July

7

3308.00

2773.81

3640.00

-866.19

91.00

176.00

0.19

9

08-11-2020

August

8

1993.00

1882.22

1760.00

122.22

44.00

11.00

0.78

10

09-11-2020

September

9

2703.00

2188.89

988.89

1200.00

89.00

168.00

0.11

11

10-11-2020

October

10

9519.00

8754.44

124.00

167.00

91.00

0.56

12

11-11-2020

November

11

4125.00

3451.59

3320.00

711.11

-579.52

83.00

64.00

190.00

-0.59

13

12-11-2020

December

12

3930.00

3541.90

2480.00

333.33

728.57

62.00

30.00

102.00

0.10

14

13-11-2020

January

13

4158.00

3612.38

1360.00

966.67

1285.71

34.00

58.00

180.00

1.62

15

14-11-2020

February

14

9239.00

8343.17

131.00

21.00

161.00

135.00

-0.17

16

14-11-2020

March

15

1000.00

0.00

1000.00