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 10, 2020

RAJESH1960 said:
Maabadi, I was going through the formula to understand it, which I always do to understand it and use that knowledge when and where ever required in future. But I just can't understand the head and tail of it. It's too advanced for me. The question I posted was only half of the full question. I thought if I got the correct answer I will be able to edit it and complete the other remaining half. But only you can help me to complete it as it is too complicated for me. If it's ok with you, can I re-post the whole question. It is just another additional 4 columns but with different taxes. Please reply and let me know. I bet this won't take more time as it is connected to the old formula. You will just have to add a few more conditions.

RAJESH1960 · Dec 10, 2020

Hey Fluff, I just wanted to ask you that whether I should post the question in a new post or can I post it here itself as it is connected to the question of this post.

maabadi · Dec 11, 2020

for your first uploaded file , one formula don't work because of circular refrences on column A.
Then I create 4 formula for 4 column:
D4:

Excel Formula:

=IF(ROW()-ROW($B$4)<4,IF($H4="","",$B4),IF($H4="","",$H4/2.5*100))

E4:

Excel Formula:

=IF(ROW()-ROW($B$4)<4,IF($J4="","",$J4/6*100),IF($D4<>"",IF($J4="","",IF(AND($L4="",$N4=""),$B4-IF($D4="",0,$D4),$J4/6*100)),IF($F4="",IF($J4="","",$J4/6*100))))

F4:

Excel Formula:

=IF(OR($D4<>"",$E4<>""),IF($L4="","",IF($N4="",$B4-IF($D4="",0,$D4)-IF($E4="",0,$E4),$L4/9*100)),IF(ROW()-ROW($B$4)<4,IF($L4="","",$B4),$L4/2.5*100))

G4:

Excel Formula:

=IF(AND(D4="",E4="",F4=""),IF($N4="","",$N4/14*100),IF(COLUMN()-COLUMN($B$4)=5,IF($N4="","",$B4-IF($D4="",0,$D4)-IF($E4="",0,$E4)-IF($F4="",0,$F4)),IF(ROW()-ROW($B$4)<4,IF($N4="","",$B4),$N4/14*100)))

RAJESH1960 · Dec 11, 2020

maabadi said:

for your first uploaded file , one formula don't work because of circular refrences on column A.
Then I create 4 formula for 4 column:
D4:

Excel Formula:

=IF(ROW()-ROW($B$4)<4,IF($H4="","",$B4),IF($H4="","",$H4/2.5*100))

E4:

Excel Formula:

=IF(ROW()-ROW($B$4)<4,IF($J4="","",$J4/6*100),IF($D4<>"",IF($J4="","",IF(AND($L4="",$N4=""),$B4-IF($D4="",0,$D4),$J4/6*100)),IF($F4="",IF($J4="","",$J4/6*100))))

F4:

Excel Formula:

=IF(OR($D4<>"",$E4<>""),IF($L4="","",IF($N4="",$B4-IF($D4="",0,$D4)-IF($E4="",0,$E4),$L4/9*100)),IF(ROW()-ROW($B$4)<4,IF($L4="","",$B4),$L4/2.5*100))

G4:

Excel Formula:

=IF(AND(D4="",E4="",F4=""),IF($N4="","",$N4/14*100),IF(COLUMN()-COLUMN($B$4)=5,IF($N4="","",$B4-IF($D4="",0,$D4)-IF($E4="",0,$E4)-IF($F4="",0,$F4)),IF(ROW()-ROW($B$4)<4,IF($N4="","",$B4),$N4/14*100)))

The formula you sent earlier is perfect. I don't know why you changed the formula and this new formula doesn't work at all. I am getting a blank cell in all the cells. You can forget about the earlier question as it is solved.

RAJESH1960 · Dec 11, 2020

I am sending you a new file. Would like a single formula in H2 which I can drag it till O2 and drag the formula to the last cell.
If the column G is blank then it should take the formula from cells P R T and U, which you have already solved but there is an additional column with the heading Type B, which will change the whole range in your formula.

If the column G is not blank then it should take the formula from cells X Y Z and AA with different percentages 5%, 12%, 18% and 28 %

Please note that the percentages in the first half were 2.5%, 6%, 9% and 14%.

The amount in column F is divided in the columns H I J and K proportionately as per their percentage. At the same time, the amount in column G is divided in the columns L M N and O again as per their percentage.

In simple terms the amount in Column F = sum of columns H I J and K AND Column G is the sum of columns L M N and O.

full query.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

1

Date

Particulars

No.

XYZ

Gross Total

TYPE 1 SUB TOTAL

TYPE 2 SUB TOTAL

TYPE 1 -@ 5%

TYPE 1 -@ 12%

TYPE 1 -@ 18%

TYPE 1 -@ 28%

TYPE 2 @ 5%

TYPE 2 @ 12%

TYPE 2 @ 18%

TYPE 2 @ 28%

2.5

6

9

14

5

12

18

28

Round Off

Diff

2

01-11-2020

January

1

GHIJ123

1930.00

1840.00

46.00

-1842.00

3

02-11-2020

February

2

ABCC1235

3528.00

3150.00

189.00

-3150.00

4

03-11-2020

March

3

12ABC225

2622.00

2222.22

200.00

-2222.44

5

04-11-2020

April

4

ABCD

1033.00

807.14

113.00

-807.29

6

05-11-2020

May

5

3626.00

3310.00

1160.00

2150.00

29.00

129.00

-3310.00

7

06-11-2020

June

6

BCDE

5560.00

5191.43

4720.00

471.43

118.00

66.00

-5190.86

8

07-11-2020

July

7

CDEF

3308.00

2773.81

1516.67

1257.14

91.00

176.00

-2773.62

9

08-11-2020

August

8

GHIJ123

1993.00

1882.22

1760.00

122.22

44.00

11.00

-1881.44

10

09-11-2020

September

9

2703.00

2188.89

988.89

1200.00

89.00

168.00

-2188.78

11

10-11-2020

October

10

12ABC225

9519.00

8754.44

4800.00

2783.33

1171.11

120.00

167.00

91.00

-8745.89

12

11-11-2020

November

11

4125.00

3451.59

1383.33

711.11

1357.14

83.00

64.00

190.00

-3452.17

13

12-11-2020

December

12

3930.00

3541.90

2480.00

333.33

728.57

62.00

30.00

102.00

-3541.81

14

13-11-2020

January

13

12ABC225

4158.00

3612.38

1360.00

966.67

1285.71

34.00

58.00

180.00

-3610.76

15

14-11-2020

February

14

ABCD

9239.00

8343.17

131.00

21.00

161.00

135.00

-8343.35

16

16-11-2020

April

16

GHIJ123

1930.00

1840.00

92.00

-1842.00

17

17-11-2020

May

17

3528.00

3150.00

378.00

-3150.00

18

18-11-2020

June

18

12ABC225

2622.00

2222.22

400.00

-2222.44

19

19-11-2020

July

19

ABCD

1033.00

807.14

226.00

-807.29