MULTIPLE IF AND OR FUNCTIONS

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. 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
ABCDEFGHJLNPQRSTUVWX
1STATIC VALUEVARIABLE COLUMNSCOLUMNS WHERE THE FORMULA TO BE ENTEREDVARIABLE COLUMNS
2GRAND TOTALA TOTALB TOTALABCDABCDR/ODiff
3Rows for reference onlyxx51218282.56914
41051001002.5-50ROW 4ROW 5ROW 6ROW 7
51121001006-120IF B4="",""IF B5="",""IF B6="",""IF B7="",""
61181001009-180IF J4,L4 AND N4=""IF H5, L5 AND N5=""IF H6,J6 AND N6=""IFJ7, J7 AND L7=""
712810010014-280THEN E4,F4 AND G4=""THEN D5,F5 AND G5="" THEN D6, E6 AND G6=""THEN D7,E7 AND F7=""
82172001001002.56-170AND D4=B4AND E5=N5/6*100AND F6=P6/9*100AND G7=R7/14*100
923020010010069-300
10246200100100914-460ROW 8ROW 9ROW 10ROW 11
112232001001002.59-230IF B8="",""IF B9="",""IF B10="",""IF B11="",""
122332001001002.514-330IF L8 AND N8=""IF H9 AND N9=""IF H10 AND J10=""IF J11 AND N11=""
133353001001001002.569-350THEN F8 AND G8 =""THEN D9 AND G9=""THEN D10 AND E10=""THEN E11 AND G11 =""
143583001001001006914-580AND D8=H8/2.5*100AND E9=J9/6*100AND F10=L10/9*100AND D11=H11/2.5*100
153513001001001002.5914-510ANDAND ANDAND
163453001001001002.5614-450F8=B8-D8G8=B9-E9G10=B10-F10F11=B11-D11
174634001001001001002.56914-630
18240200100100614-400ROW 12ROW 13ROW 14ROW 15
19IF B12="",""IF B13="",""IF B14="",""IF B15="",""
20I WILL TRY TO EXPLAIN MY PROBLEM AS SIMPLE AS POSSIBLEIF J12 AND L12=""IF N13=""IF H14=""IF J15=""
21If you change the amount in column B the difference amount in column Y should be zero onlyTHEN E12 AND F12 =""THAN G13=""THAN D14=""THAN E15=""
22I have given different formulas manually in columns D to KAND D12=H12/2.5*100THEN D13=H13/2.5*100AND E14=J14/6*100AND D15=H15/2.5*100
23There can be 15 different calculations for each amount entered in Column B ANDANDANDAND
24The number of entries are in 1000's. I have been calculating it manually for each row using sort and filter optionsG12=B12=D12E13=J13/6*100F14=L14/9*100F15=L15/9*100
25I 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 entryANDANDAND
26Due to lack of knowledge of multiple "IF", "AND" & "OR", I am finding it difficult to solve this. I would like to know and understandF13=B13-H13-J13G14=B14-J14-L14G15=B15-D15-L15
27Since the last few weeks I have been trying but not able to solve it. I would really appreciate any help in solving this.
28Why don't you experts give it a try. If the solution is solved it will save me hours of workROW 16ROW 17ROW 18
29Please note that the amounts in columns H:P are also variables like in column BIF B16="",""IF B17="",""IF B18="",""
30Column C is an extension of my project and it will require to change only the references once I find a solutionIF J16=""IF H17, J17,L17 AND N17<>""IF H18 AND L18 =""
31How it worksTHEN F16=""THEN D17=H16/2.5*100THEN D18="", F18=""
32In columns H,J,L and N if there is amount in one cell only then it takes the amount from column BAND D16=H16/2.5*100AND E17=J17/6*100AND E18=J18/6*100
33if 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 ANDAND F17=L17/9*100AND G18=B18-E18
34and the second cell amount should be taken after deducting the total amount from the first calculated amountF16= L16/9*100AND G17=B17-D17-E17-F17
35if there are amounts in 3 cells the again the first cell amount should be calculated from the corresponding column AND
36the second amount also to be calculated from the corresponding columnG16=B16-D16-F16
37and then in the third cell it should deduct the total amount from the first and second calculation amounts
38if there are amounts in all 4 cells then the first cell amount should be calculated from the corresponding column
39the second cell amount also to be calculated from the corresponding column
40the third cell amount also to be calculated from the corresponding column
41and the fourth cell it should deduct the total amount from the first, second and third calculated amounts
42
43
44
45
QUERY
Cell Formulas
RangeFormula
D4D4=B4
P4:P18P4=B4+C4-SUM(D4:O4)
Q4:Q18Q4=A4-SUM(D4:O4)
E5E5=B5
F6F6=B6
G7G7=B7
D8,D15:D17,D11:D13D8=H8/2.5*100
E8E8=B8-D8
E9,E16:E17,E13:E14E9=J9/6*100
G10G10=B10-F10
F9F9=B9-E9
F10,F17,F14:F15F10=L10/9*100
F11F11=B11-D11
G12G12=B12-D12
F13F13=B13-D13-E13
G14G14=B14-E14-F14
G15G15=B15-D15-F15
G16G16=B16-D16-E16
A4:A17A4=SUM(C4:O4)
G17G17=B17-D17-E17-F17
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,604
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
Excel Formula:
=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:
Excel Formula:
=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

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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.
Excel Formula:
=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:
Excel Formula:
=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

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,956

ADVERTISEMENT

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

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
I will prepare and post a new sheet as the old sheet got deleted.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

new query mr.excel.xlsx
ABCDEFGHIJKLMNOPQRSTU
1DateParticularsVch No.xyzGROSS AMOUNTSUB TOTAL A5% A12% A18% A28% AVARIABLES 2.5%VARIABLES 2.5%VARIABLES 6%VARIABLES 6%VARIABLES 9%VARIABLES 9%VARIABLES 14%VARIABLES 14%Round OffDiff
201-11-2020January11930.001840.001840.00   46.0046.00-2.000.00
302-11-2020February23528.003150.00 3150.00  189.00189.000.000.00
403-11-2020March31311.002222.22  2222.22 200.00100.00-1211.220.00
504-11-2020April41033.00807.14   807.14113.00113.00-0.140.00
605-11-2020May53626.003310.001160.002150.00  29.0029.00129.00129.000.000.00
706-11-2020June65560.005191.434720.00  471.43118.00118.0066.0066.000.570.00
807-11-2020July73308.002773.81 1516.67 1257.1491.0091.00176.00176.000.190.00
908-11-2020August81993.001882.221760.00 122.22 44.0044.0011.0011.000.780.00
1009-11-2020September92703.002188.89  988.891200.0089.0089.00168.00168.000.110.00
1110-11-2020October109519.008754.444960.002783.335971.11 124.00124.00167.00167.0091.0091.00-4959.440.00
1211-11-2020November114125.003451.59 1383.33711.111357.1483.0083.0064.0064.00190.00190.00-0.590.00
1312-11-2020December123930.003541.902480.00 333.33728.5762.0062.0030.0030.00102.00102.000.100.00
1413-11-2020January134158.003612.381360.00966.67 1285.7134.0034.0058.0058.00180.00180.001.620.00
1514-11-2020February149239.008343.175240.00350.001788.89964.29131.00131.0021.0021.00161.00161.00135.00135.00-0.170.00
1614-11-2020March151000.000.001000.000.00
17Grand Total56,963.0051,069.2123,520.0012,300.0012,137.788,071.43588.00588.00738.00738.00646.00546.001,130.001,130.00(5,170.21)
18
19
20THE COLUMNS G H I J IN THE ORIGINAL DATA ARE BLANK AND ARE CALCULATED MANUALLY.
21IF 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
22IF THERE ARE MORE THAN ONE VALUES IN COLUMNS K M O Q THEN
23THE FIRST VALUE IS CALCULATED BY A FORMULA AS SHOWN IN COLUMN G H I J
24AND THE LAST AMOUNT IS TAKEN AFTER DEDUCTING THE SUBTOTAL FROM THE VALUE OF THE CALCULATED OTHER CELL
25THIS WAY THE GROSS TOTAL AND SUB TOTAL AND DIFFERENCE AMOUNTS WILL NOT CHANGE
26
27TO 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
28EXAMPLE 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
29THAT IS WHY THE SECOND VALUE OF THE CELL IN M6 SHOULD BE CALCULATED BY DEDUCTING THE VALUE IN F6 WITH K6
30SAME 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
32I WANT TO REPLACE THE FORMULA IN G H I J WITH ONE SINGLE FORMULA
33PLEASE 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
RangeFormula
G2G2=F2
H2,H7:H15,H4:H5H2=IF(M2="","",M2/6*100)
I12:I15,I10,I5:I8,I2:I3I2=IF(O2="","",O2/9*100)
J11,J9,J6,J2:J4J2=IF(Q2="","",Q2/14*100)
G3:G15G3=IF(K3="","",K3/2.5*100)
H3H3=F3
I4I4=F4
J5J5=F5
H6H6=F6-G6
J7J7=F7-G7
J8J8=F8-H8
I9I9=F9-G9
J10J10=F10-I10
I11I11=F11-H11
J12J12=F12-H12-I12
J13J13=F13-G13-I13
J14J14=F14-G14-H14
J15J15=F15-G15-H15-I15
S2:S16S2=E2-SUM(G2:R2)
T2:T16T2=E2-SUM(G2:S2)
E17:S17E17=SUM(E2:E16)
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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.
Excel Formula:
=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:
Excel Formula:
=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

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
Maabadi, I have posted again a different sheet. Please check
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,370
Office Version
  1. 365
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,252
Messages
5,623,624
Members
415,981
Latest member
Baltwin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top