MULTIPLE IF AND OR FUNCTIONS

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
453
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
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
453
Office Version
  1. 2019
Platform
  1. Windows
It is problem with Don't fixing Cell Address at first column. I correct it.
For F2 you could Change $F$2 to $F2, Never mind, I Change it.
Excel Formula:
=IF(COLUMN()-COLUMN($F2)=2,IF($P2="","",IF(AND($R2="",$T2="",$V2=""),$F2,$P2/2.5*100)),IF(COLUMN()-COLUMN($F2)=3,IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100)))),IF(COLUMN()-COLUMN($F2)=4,IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100)))),IF(COLUMN()-COLUMN($F2)=5,IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2)))),IF(COLUMN()-COLUMN($G2)=5,IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100)),IF(COLUMN()-COLUMN($G2)=6,IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100)))),IF(COLUMN()-COLUMN($G2)=7,IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100)))),IF(COLUMN()-COLUMN($G2)=8,IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2)))),""))))))))

If you want 2 different formula Use These:
Excel Formula:
=IF(COLUMN()-COLUMN($F2)=2,IF($P2="","",IF(AND($R2="",$T2="",$V2=""),$F2,$P2/2.5*100)),IF(COLUMN()-COLUMN($F2)=3,IF($R2="","",IF(AND($P2="",$T2="",$V2=""),$F2,IF($P2="",$R2/6*100,IF(AND($T2="",$V2=""),$F2-IF($H2="",0,$H2),$R2/6*100)))),IF(COLUMN()-COLUMN($F2)=4,IF($T2="","",IF(AND($P2="",$R2="",$V2=""),$F2,IF(AND($P2="",$R2=""),$T2/9*100,IF($V2="",$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)),$T2/9*100)))),IF(COLUMN()-COLUMN($F2)=5,IF($V2="","",IF(AND($P2="",$R2="",$T2=""),$F2,$F2-(IF($H2="",0,$H2)+IF($I2="",0,$I2)+IF($J2="",0,$J2)))),""))))

AND
Excel Formula:
=IF(COLUMN()-COLUMN($G2)=5,IF($X2="","",IF(AND($Y2="",$Z2="",$AA2=""),$G2,$X2/5*100)),IF(COLUMN()-COLUMN($G2)=6,IF($Y2="","",IF(AND($X2="",$Z2="",$AA2=""),$G2,IF($X2="",$Y2/12*100,IF(AND($Z2="",$AA2=""),$G2-IF($L2="",0,$L2),$Y2/12*100)))),IF(COLUMN()-COLUMN($G2)=7,IF($Z2="","",IF(AND($X2="",$Y2="",$AA2=""),$G2,IF(AND($X2="",$Y2=""),$Z2/18*100,IF($AA2="",$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)),$Z2/18*100)))),IF(COLUMN()-COLUMN($G2)=8,IF($AA2="","",IF(AND($X2="",$Y2="",$Z2=""),$G2,$G2-(IF($L2="",0,$L2)+IF($M2="",0,$M2)+IF($N2="",0,$N2)))),""))))
PHEW!!!! Finally!!!! The 2 formulas are perfect. I have checked it in more than 2 data and it's working just perfect. Thanks Maabadi, for your patience and holding me up till the end.
The biggest issue with me is that I know what I want, but it is very difficult to explain and make someone understand. But you did understand step by step.
Thank You once again.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,616
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Your Welcome again & good luck.
You can also check the one formula for whole rows. It doesn't show errors also.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
453
Office Version
  1. 2019
Platform
  1. Windows
Your Welcome again & good luck.
You can also check the one formula for whole rows. It doesn't show errors also.
Wow!! That is great work man. Actually I needed that single formula in the first place only. I too was going to try that. in my next lesson, If F2="","", by replacing the second "" with the 2nd formula. But you did it for me. You are really an expert buddy. Hope to see you soon with my next question. Till then, enjoy.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,699
Members
416,042
Latest member
Oden

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