MULTIPLE IF AND OR FUNCTIONS

[RAJESH1960]

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=B8-D8	G8=B9-E9	G10=B10-F10	F11=B11-D11
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=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

 

[maabadi]

I Use this formula for D4:G15 because all Data equal 100 (if all of Data in these column is 100 it works). Input at D4 and then Drag it right & down.

=IF(ROW()<8,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",100))

Or You can Use for Other numbers Except 100:

=IF(ROW()<8,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4/COUNT($H4,$J4,$L4,$N4)))

 

[RAJESH1960]

I Use this formula for D4:G15 because all Data equal 100 (if all of Data in these column is 100 it works). Input at D4 and then Drag it right & down.

=IF(ROW()<8,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",100))

Or You can Use for Other numbers Except 100:

=IF(ROW()<8,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4/COUNT($H4,$J4,$L4,$N4)))

 

[RAJESH1960]

Thank you so much Maabadi for your interest to answer my question. But you have got my question wrong. The amount in columns J L N P are variables, it can have any figure from 0.01 to any number and even may be blank. If any of the columns are empty then it should display blank or zero in the corresponding D E F G columns. I will rephrase the question and post a new sheet for you to understand better. There are 18 different types of entries and so there have to be at least 18 conditions to be fulfilled in one cell.

 

[mikerickson]

How about in D4
=IF(J4="", "", $B4/COUNT($j4:$P4))
dragged right and down

 

[RAJESH1960]

I will prepare and post a new sheet as the old sheet got deleted.

 

[RAJESH1960]

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 2.5%	VARIABLES 6%	VARIABLES 6%	VARIABLES 9%	VARIABLES 9%	VARIABLES 14%	VARIABLES 14%	Round Off	Diff
2	01-11-2020	January	1		1930.00	1840.00	1840.00				46.00	46.00							-2.00	0.00
3	02-11-2020	February	2		3528.00	3150.00		3150.00					189.00	189.00					0.00	0.00
4	03-11-2020	March	3		1311.00	2222.22			2222.22						200.00	100.00			-1211.22	0.00
5	04-11-2020	April	4		1033.00	807.14				807.14							113.00	113.00	-0.14	0.00
6	05-11-2020	May	5		3626.00	3310.00	1160.00	2150.00			29.00	29.00	129.00	129.00					0.00	0.00
7	06-11-2020	June	6		5560.00	5191.43	4720.00			471.43	118.00	118.00					66.00	66.00	0.57	0.00
8	07-11-2020	July	7		3308.00	2773.81		1516.67		1257.14			91.00	91.00			176.00	176.00	0.19	0.00
9	08-11-2020	August	8		1993.00	1882.22	1760.00		122.22		44.00	44.00			11.00	11.00			0.78	0.00
10	09-11-2020	September	9		2703.00	2188.89			988.89	1200.00					89.00	89.00	168.00	168.00	0.11	0.00
11	10-11-2020	October	10		9519.00	8754.44	4960.00	2783.33	5971.11		124.00	124.00	167.00	167.00	91.00	91.00			-4959.44	0.00
12	11-11-2020	November	11		4125.00	3451.59		1383.33	711.11	1357.14			83.00	83.00	64.00	64.00	190.00	190.00	-0.59	0.00
13	12-11-2020	December	12		3930.00	3541.90	2480.00		333.33	728.57	62.00	62.00			30.00	30.00	102.00	102.00	0.10	0.00
14	13-11-2020	January	13		4158.00	3612.38	1360.00	966.67		1285.71	34.00	34.00	58.00	58.00			180.00	180.00	1.62	0.00
15	14-11-2020	February	14		9239.00	8343.17	5240.00	350.00	1788.89	964.29	131.00	131.00	21.00	21.00	161.00	161.00	135.00	135.00	-0.17	0.00
16	14-11-2020	March	15		1000.00	0.00													1000.00	0.00
17		Grand Total			56,963.00	51,069.21	23,520.00	12,300.00	12,137.78	8,071.43	588.00	588.00	738.00	738.00	646.00	546.00	1,130.00	1,130.00	(5,170.21)
18
19
20	THE COLUMNS G H I J IN THE ORIGINAL DATA ARE BLANK AND ARE CALCULATED MANUALLY.
21	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
22	IF THERE ARE MORE THAN ONE VALUES IN COLUMNS K M O Q THEN
23	THE FIRST VALUE IS CALCULATED BY A FORMULA AS SHOWN IN COLUMN G H I J
24	AND THE LAST AMOUNT IS TAKEN AFTER DEDUCTING THE SUBTOTAL FROM THE VALUE OF THE CALCULATED OTHER CELL
25	THIS WAY THE GROSS TOTAL AND SUB TOTAL AND DIFFERENCE AMOUNTS WILL NOT CHANGE
26
27	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
28	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
29	THAT IS WHY THE SECOND VALUE OF THE CELL IN M6 SHOULD BE CALCULATED BY DEDUCTING THE VALUE IN F6 WITH K6
30	SAME WAY WHERE THE WHITE CELLS HAVE VALUES IN MORE THAN ONE COLUMN THEN THE LAST VALUE SHOULD BE CALCULATED BY DEDUCTING SUB TOTAL FROM THE SUM OF VALUES OF CALCULATED VALUES
31
32	I WANT TO REPLACE THE FORMULA IN G H I J WITH ONE SINGLE FORMULA
33	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
34
35
36
37
38
39
40
QUERY

Cell Formulas
Range	Formula
G2	G2	=F2
H2,H7:H15,H4:H5	H2	=IF(M2="","",M2/6*100)
I12:I15,I10,I5:I8,I2:I3	I2	=IF(O2="","",O2/9*100)
J11,J9,J6,J2:J4	J2	=IF(Q2="","",Q2/14*100)
G3:G15	G3	=IF(K3="","",K3/2.5*100)
H3	H3	=F3
I4	I4	=F4
J5	J5	=F5
H6	H6	=F6-G6
J7	J7	=F7-G7
J8	J8	=F8-H8
I9	I9	=F9-G9
J10	J10	=F10-I10
I11	I11	=F11-H11
J12	J12	=F12-H12-I12
J13	J13	=F13-G13-I13
J14	J14	=F14-G14-H14
J15	J15	=F15-G15-H15-I15
S2:S16	S2	=E2-SUM(G2:R2)
T2:T16	T2	=E2-SUM(G2:S2)
E17:S17	E17	=SUM(E2:E16)

 

[RAJESH1960]

I Use this formula for D4:G15 because all Data equal 100 (if all of Data in these column is 100 it works). Input at D4 and then Drag it right & down.

=IF(ROW()<8,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",100))

Or You can Use for Other numbers Except 100:

=IF(ROW()<8,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$B4/COUNT($H4,$J4,$L4,$N4)))[/COD
[/QUOTE]

 

[RAJESH1960]

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

 

[Fluff]

@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