MULTIPLE IF AND OR FUNCTIONS

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
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
 
Sort by date Sort by votes
RAJESH1960 said:
You should change the beginning of the code I think. =IF($F2="","",....
If $F2="",then take the value from $G2 and apply the formula in L M N O, else take the value from $F2 and apply the formula in H I J K.
I will give it a try and let you know if it worked. Your 2 different formulas concept also is working.
Click to expand...
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I think there is still a problem in the formula. When I tried it on the original data, it is showing some errors. Got to go, will let you know tonight.
 
Upvote 0
Hi Good Evening Maabadi, I would like to share some notes with you regarding your answer. First of all the data in the cells E, F, G, P to AA will not be the same, but the calculated and posted formula in H to O, should be the same to match the amounts.
The formula you sent is working in the query sheet but when I pasted the formula in the original data, I found out that the formula is incorrect.
First thing, the columns “Gross Total”, “Roundoff” and Diff” are no where connected to the formulas in columns H I J K L M N and O. It is just for calculation purpose. When I press F2 in the 3rd or any of the below columns where the formula is entered. it has a fixed reference to a cell F2 in case of columns H I J K and and L2 in case of columns L M N and O. Since the cell reference is not connected to that particular row, I think that too may be a mistake.
For inserting a formula, only Columns F H I J K P Q R S T U V and W are inter-related and connected with that row only.Likewise, Columns G L M N O P X Y Z AND AA are inter-related and connected to that row only.
In the formula you have mentioned E2=””, as I said that column E in not at all connected for the calculation of the formula
To understand better, Please sort the data in the sheet and check, the answers will change and show errors.
 
Upvote 0
I am sharing a few images for your reference, which I think will help to solve the problem.
 

Attachments

  • check 1.png
    check 1.png
    27.5 KB · Views: 8
  • Check 2.png
    Check 2.png
    37.3 KB · Views: 8
  • check 3.png
    check 3.png
    25.9 KB · Views: 8
Upvote 0
Yes. I do them because circular references. but they don't use at calculation for that column. they see because autofiled between Columns.
For example in This formula:
1. Bold Part Uses for column H
2. Blue Part + Bold Part uses for column I
3. Red Part + Bold Part uses for column J
4. Green Part + Bold Part uses for column K
and these parts calculation at that column don't conflict with the other columns.

=IF(AND(COLUMN()-COLUMN($F$2)=5,G2="",E2="",F2=""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",INDIRECT(ADDRESS(ROW(),2*COLUMN()))/14*100),IF(COLUMN()-COLUMN($F$2)=5,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$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)=4,OR(F2<>"",G2<>"")),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()+2))=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()))/9*100)),IF(AND(COLUMN()-COLUMN($F$2)=3,G2<>""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",IF(AND(INDIRECT(ADDRESS(ROW(),2*COLUMN()+2))=0,INDIRECT(ADDRESS(ROW(),2*COLUMN()+4))=0),$F2-$H2,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/6*100)),IF(ROW()-ROW($F$2)<4,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$F2),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",IF(COLUMN()-COLUMN($F$2)=4,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/9*100,IF(COLUMN()-COLUMN($F$2)=3,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/6*100,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/2.5*100))))))))
 
Last edited:
Upvote 0
maabadi said:
Yes. I do them because circular references. but they don't use at calculation for that column. they see because autofiled between Columns.
For example in This formula:
1. Bold Part Uses for column H
2. Blue Part + Bold Part uses for column I
3. Red Part + Bold Part uses for column J
4. Green Part + Bold Part uses for column K
and these parts calculation at that column don't conflict with the other columns.

=IF(AND(COLUMN()-COLUMN($F$2)=5,G2="",E2="",F2=""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",INDIRECT(ADDRESS(ROW(),2*COLUMN()))/14*100),IF(COLUMN()-COLUMN($F$2)=5,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$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)=4,OR(F2<>"",G2<>"")),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()+2))=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()))/9*100)),IF(AND(COLUMN()-COLUMN($F$2)=3,G2<>""),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",IF(AND(INDIRECT(ADDRESS(ROW(),2*COLUMN()+2))=0,INDIRECT(ADDRESS(ROW(),2*COLUMN()+4))=0),$F2-$H2,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/6*100)),IF(ROW()-ROW($F$2)<4,IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",$F2),IF(INDIRECT(ADDRESS(ROW(),2*COLUMN()))=0,"",IF(COLUMN()-COLUMN($F$2)=4,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/9*100,IF(COLUMN()-COLUMN($F$2)=3,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/6*100,INDIRECT(ADDRESS(ROW(),2*COLUMN()))/2.5*100))))))))
Click to expand...
Maabadi, Your formula is perfect. It's working correctly. One step to stop the figures from changing when I sort is to copy the range of cells with the formula and paste it in the same cells using paste special. The message marked as solution are the right formulas. Thank You once again not only for the formula but also for sharing your valuable time.
 
Upvote 0
Maabadi, I was trying to understand your formula. The first formula doesn't have any reference to columns P to U and is Ok, The problem is in the second formula which has reference to U to AA. whereas the columns U to W are not at all connected to Type B column but are referred to in the formula. Please check the sheet once again.
Press F2 in column L M N O one at a time and please check. The problem is, when working on a huge data, I am getting a circular reference. If you remove the reference of cells U, V, W from the second formula, I think it will solve the problem. One more thing, Please, if possible, remove the references from column AB and AC as they are inserted for reference only and are deleted later.
DateParticularsNo.XYZGross TotalTYPE 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.52.5669914145121828Round OffDiff
1033.00807.14807.14807.14-1388.43
0.00​
1930.001840.001840.001840.00-3590.00
0.00​
1993.001882.221882.221882.22-3653.67
0.00​
2703.002188.892188.892188.89-3863.67
0.00​
2622.002222.222222.222222.22-4044.67
0.00​
3308.002773.812773.812773.81-5013.43
0.00​
3528.003150.003150.003150.00-5922.00
0.00​
3626.003310.003310.003310.00-6304.00
0.00​
4125.003451.593451.593451.59-6229.76
0.00​
3930.003541.903541.903541.90-6695.71
0.00​
4158.003612.383612.383612.38-6679.14
0.00​
5560.005191.435191.435191.43-10014.29
0.00​
9239.008343.178343.178343.17-15790.52
0.00​
9519.008754.44100.00100.00100.008454.445.0012.0018.0028.00-8052.89
0.00​
1930.001840.001840.0046.0046.00-1842.00
0.00​
3528.003150.003150.00189.00189.00-3150.00
0.00​
 
Upvote 0
RAJESH1960 said:
Maabadi, I was trying to understand your formula. The first formula doesn't have any reference to columns P to U and is Ok, The problem is in the second formula which has reference to U to AA. whereas the columns U to W are not at all connected to Type B column but are referred to in the formula. Please check the sheet once again.
Press F2 in column L M N O one at a time and please check. The problem is, when working on a huge data, I am getting a circular reference. If you remove the reference of cells U, V, W from the second formula, I think it will solve the problem. One more thing, Please, if possible, remove the references from column AB and AC as they are inserted for reference only and are deleted later.
DateParticularsNo.XYZGross TotalTYPE 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.52.5669914145121828Round OffDiff
1033.00807.14807.14807.14-1388.43
0.00​
1930.001840.001840.001840.00-3590.00
0.00​
1993.001882.221882.221882.22-3653.67
0.00​
2703.002188.892188.892188.89-3863.67
0.00​
2622.002222.222222.222222.22-4044.67
0.00​
3308.002773.812773.812773.81-5013.43
0.00​
3528.003150.003150.003150.00-5922.00
0.00​
3626.003310.003310.003310.00-6304.00
0.00​
4125.003451.593451.593451.59-6229.76
0.00​
3930.003541.903541.903541.90-6695.71
0.00​
4158.003612.383612.383612.38-6679.14
0.00​
5560.005191.435191.435191.43-10014.29
0.00​
9239.008343.178343.178343.17-15790.52
0.00​
9519.008754.44100.00100.00100.008454.445.0012.0018.0028.00-8052.89
0.00​
1930.001840.001840.0046.0046.00-1842.00
0.00​
3528.003150.003150.00189.00189.00-3150.00
0.00​
Click to expand...
In Type A too, there are mistakes in the answers.?
 
Upvote 0
RAJESH1960 said:
In Type A too, there are mistakes in the answers.?
Click to expand...
This is a edited copy of the original data. Please Check the columns F and H . They should have the same amount.
 

Attachments

  • mistake.png
    mistake.png
    35.9 KB · Views: 5
Upvote 0
I think with this situation it is better tp use formula for each column seperately, to haven't circular references. Then i work on 8 formula for 8 column and post here.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Parent/Child visual help
Replies
1
Views
106
Peter_SSs
Peter_SSs
F
  • Question
Conditional formatting help
Replies
6
Views
87
Fegal
F
A
  • Solved
Calculating income tax in reverse
Replies
9
Views
220
Dave Patton
D
esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
542
esset
esset
N
  • Question
Count - specific consecutive order
Replies
6
Views
478
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,523
Messages
6,120,028
Members
448,940
Latest member
mdusw

Share this page

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
Back
Top