Complicated IF AND Statement Help

C

Cantrecallmyusername

Board Regular
Joined
May 24, 2021
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I am trying to create a formula that hits a number of parameters and after several attempts I am not sure if it is actually possible - the formula needs to combine quite a number of parameters to work;
The premise for the formula is a s/s to track when people leave/join -

  • hours are calculated per column based on the values in cells M2/M3 * column L (depending on region)
  • when someone leave and the date matches M5 the value should be zero and continue to be zero going to the right (the date will change in each column though)
    • I thought about handling this by including an extra IF statement once the below is correctly designed where the column N looks to the left and if the value in M is zero it will return a zero
  • when someone joins and the date matches M5 the value should be calculated using the hours per their region (he date will change in each column though)
    • Not sure on how to handle this one at the moment

  1. IF Column F = "No" AND Column J = "Space" - M2*L6
  2. IF Column F = "No" AND Column J = "Earth" - M3*L6
  3. IF Column G = "Y" AND Column H = M5 AND Column J = "Space" - M2*L6
  4. IF Column G = "Y" AND Column H = M5 AND Column J = "Earth" - M3*L6
  5. IF Column F = M5 = 0

My formula works in places but I think as I have to many similar outcomes it is difficult to ascertain a true FALSE - as seen in the example shared it pushes the value to 0 when not meeting all the sperate condtions. Any help greatly appreciated.

Excel Formula: 
=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6="",J6="SPACE"),$M$2*L6,0)))))

Book1
ABCDEFGHIJKLM
1
236.5
335.0
4
5ResourceRoleRole#2LeaverJoinerRegionGap1No.08/07/2022
6Donald DuckDuckDuckNoSPACE136.5
7Bugs BunnyBunnyBunnyNoYSPACE10.0
8PlutoDogDog08/07/2022EARTH10.0
9
10
11
Sheet1
Cell Formulas
RangeFormula
M6:M8M6=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6="",J6="SPACE"),$M$2*L6,0)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A7:A8Dates OccurringyesterdaytextNO
A7:A8Cell ValueduplicatestextNO
A5:A6Dates OccurringyesterdaytextNO
A5:A6Cell ValueduplicatestextNO
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
The only thing I can see which is getting past your formula is a check for when both columns F and G are populated.

On row 7 you are getting "0" because the formula IF(AND(F6="No",G7="",J7="EARTH" is only looking for G7 to be empty, you could amend to G7>="" and then this will disregard the joiner being populated or not.

You may want a warning prompt to show when both leaver and joiner have been used, in this case just leave the fomula as is but change the last "0" in the formula to the warning you would like such as "Both joiner and leaver used"
 
Upvote 0
MiniFav said:
The only thing I can see which is getting past your formula is a check for when both columns F and G are populated.

On row 7 you are getting "0" because the formula IF(AND(F6="No",G7="",J7="EARTH" is only looking for G7 to be empty, you could amend to G7>="" and then this will disregard the joiner being populated or not.

You may want a warning prompt to show when both leaver and joiner have been used, in this case just leave the fomula as is but change the last "0" in the formula to the warning you would like such as "Both joiner and leaver used"
Click to expand...
Hi - thanks for the reply. That has fixed the issue in row 7 however still not getting the fully desired outcomes. You can see in Row 8 I have added a date which does not match with cell M5 though it is still calculating regardless of the case. In this instance I would expect cell M8 to be 0.

Book1
ABCDEFGHIJKLM
1
236.5
335.0
4
5ResourceRoleRole#2LeaverJoinerRegionGap1No.08/07/2022
6Donald DuckDuckDuckNoSPACE136.5
7Bugs BunnyBunnyBunnyNoY08/07/2022SPACE136.5
8PlutoDogDogNoY12/08/2023EARTH135.0
9
10
11
12
Sheet1
Cell Formulas
RangeFormula
M6:M8M6=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6>="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6>="",J6="SPACE"),$M$2*L6,0)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A7:A8Dates OccurringyesterdaytextNO
A7:A8Cell ValueduplicatestextNO
A5:A6Dates OccurringyesterdaytextNO
A5:A6Cell ValueduplicatestextNO
 
Upvote 0
Hi,
What does that mean in highlighted?

=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6>="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6>="",J6="SPACE"),$M$2*L6,0)))))
 
Upvote 0
Sufiyan97 said:
Hi,
What does that mean in highlighted?

=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6>="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6>="",J6="SPACE"),$M$2*L6,0)))))
Click to expand...
This was suggested by the post by MiniFav above - not sure as to its purpose..
 
Upvote 0
Cantrecallmyusername said:
Hi - thanks for the reply. That has fixed the issue in row 7 however still not getting the fully desired outcomes. You can see in Row 8 I have added a date which does not match with cell M5 though it is still calculating regardless of the case. In this instance I would expect cell M8 to be 0.

Book1
ABCDEFGHIJKLM
1
236.5
335.0
4
5ResourceRoleRole#2LeaverJoinerRegionGap1No.08/07/2022
6Donald DuckDuckDuckNoSPACE136.5
7Bugs BunnyBunnyBunnyNoY08/07/2022SPACE136.5
8PlutoDogDogNoY12/08/2023EARTH135.0
9
10
11
12
Sheet1
Cell Formulas
RangeFormula
M6:M8M6=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6>="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6>="",J6="SPACE"),$M$2*L6,0)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A7:A8Dates OccurringyesterdaytextNO
A7:A8Cell ValueduplicatestextNO
A5:A6Dates OccurringyesterdaytextNO
A5:A6Cell ValueduplicatestextNO
Click to expand...
This is because the calculation is true elsewhere, to fix this amend the formula to the following.
 
Cell Formulas
RangeFormula
M8M8=IF(AND(G8="Y",H8=M$5,J8="EARTH"),$M$3*L8,IF(AND(G8="Y",H8=M$5,J8="SPACE"),$M$2*L8,IF(F8=M$5,0,IF(AND(F8="No",G8>="",J8="EARTH",H8=""),$M$3*L8,IF(AND(F8="No",G8>="",J8="SPACE",H8=""),$M$2*L8,0)))))


Above I have just added H8="" in the last sections "IF(AND(F8="No",G8>="",J8="EARTH",H8=""),$M$3*L8,IF(AND(F8="No",G8>="",J8="SPACE",H8=""),$M$2*L8,0)))))"
 
Upvote 0
Sufiyan97 said:
Hi,
What does that mean in highlighted?

=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6>="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6>="",J6="SPACE"),$M$2*L6,0)))))
Click to expand...
This checks if the cell is Great than or equal to nothing. easiest way to do a check for both nothing and something without overcomplicating the formula.
 
Upvote 0
MiniFav said:
This is because the calculation is true elsewhere, to fix this amend the formula to the following.
 
Cell Formulas
RangeFormula
M8M8=IF(AND(G8="Y",H8=M$5,J8="EARTH"),$M$3*L8,IF(AND(G8="Y",H8=M$5,J8="SPACE"),$M$2*L8,IF(F8=M$5,0,IF(AND(F8="No",G8>="",J8="EARTH",H8=""),$M$3*L8,IF(AND(F8="No",G8>="",J8="SPACE",H8=""),$M$2*L8,0)))))


Above I have just added H8="" in the last sections "IF(AND(F8="No",G8>="",J8="EARTH",H8=""),$M$3*L8,IF(AND(F8="No",G8>="",J8="SPACE",H8=""),$M$2*L8,0)))))"
Click to expand...
Thanks for the reply - makes sense about finding the true case earlier. I have expanded my table to the right though I am not getting the desired result from this.
In row F7 - there is a date - the result I want is if this date is not equal to the date in M5 return the calculation based on value in column M - it seems to return 0 if this populated.

Also I need to incorporate a way to handle the fact at after the condition is met for the first time that the data to the ruight is updated as needed.
i.e - when a leaver leaves (the date matches in row 5) every column thereafter the value is 0 or when a person joins the (the date matches in row 5) every column thereafter the value is calculated based on the region calculation




Workings File.xlsx
ABCDEFGHIJKLMNOPQ
1
236.5
335.0
4
5ResourceRoleRole#2LeaverJoinerRegionGap1No.03/07/202210/07/202217/07/202224/07/202231/07/2022
6Donald DuckDuckDuckNoSpace136.536.536.536.536.5
7Bugs BunnyBunnyBunny10/07/2022Space10.00.00.00.00.0
8PlutoDogDogY17/07/2022EARTH10.00.035.00.00.0
9
10Expected Results
11ResourceRoleRole#2LeaverJoinerRegionGap1No.03/07/202210/07/202217/07/202224/07/202231/07/2022
12Donald DuckDuckDuckNoSpace136.536.536.536.536.5
13Bugs BunnyBunnyBunny10/07/2022Space136.50.00.00.00.0
14PlutoDogDogY17/07/2022EARTH10.00.035.035.035.0
Sheet1
Cell Formulas
RangeFormula
M6:Q8M6=IF(AND($G6="Y",$H6=M$5,$J6="EARTH"),$M$3*$L6,IF(AND($G6="Y",$H6=M$5,$J6="SPACE"),$M$2*$L6,IF($F6=M$5,0,IF(AND($F6="No",$G6>="",$J6="EARTH",$H6=""),$M$3*$L6,IF(AND($F6="No",$G6>="",$J6="SPACE",$H6=""),$M$2*$L6,0)))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A13:A14Dates OccurringyesterdaytextNO
A13:A14Cell ValueduplicatestextNO
A11:A12Dates OccurringyesterdaytextNO
A11:A12Cell ValueduplicatestextNO
A7:A10Dates OccurringyesterdaytextNO
A7:A10Cell ValueduplicatestextNO
A5:A6Dates OccurringyesterdaytextNO
A5:A6Cell ValueduplicatestextNO
 
Upvote 0
In trying to make is simpler I may made it more complex but see how you go.
If the date join is blank or text I have set it to the minimum date in the header minus 1 and for date leave the maximum date + 1.
This simplified the last expression.
I have used your values in M2 & M3 as a lookup table. I would suggest making that an actual Excel Tables so that it dynamically expand as you add more regions.

20230126 Nested If or Vlookup Combo Cantrecallmyusername.xlsx
ABCDEFGHIJKLMNOPQ
1Lookup Table
2Space36.5
3EARTH35
4
5ResourceRoleRole#2LeaverJoinerRegionGap1No.3/07/202210/07/202217/07/202224/07/202231/07/2022
6Donald DuckDuckDuckNoSpace136.536.536.536.536.5
7Bugs BunnyBunnyBunny10/07/2022Space136.50000
8PlutoDogDogY17/07/2022EARTH100353535
Modified
Cell Formulas
RangeFormula
M6:Q8M6=LET(dtStart, MIN( FILTER( $5:$5, ISNUMBER($5:$5) ) ), dtUseJoin, IF(ISNUMBER($H6), $H6, dtStart-1), dtLast, MAX( FILTER( $5:$5, ISNUMBER($5:$5) ) ), dtUseLeave, IF(ISNUMBER($F6), $F6, dtLast+1), hrsToUse, XLOOKUP($J6, $L$2:$L$3, $M$2:$M$3, 0), multiplier, $L6, IF( AND(dtUseJoin <= M$5, dtUseLeave > M$5),hrsToUse * multiplier,0))
 
Upvote 0
Solution
Alex Blakenburg said:
In trying to make is simpler I may made it more complex but see how you go.
If the date join is blank or text I have set it to the minimum date in the header minus 1 and for date leave the maximum date + 1.
This simplified the last expression.
I have used your values in M2 & M3 as a lookup table. I would suggest making that an actual Excel Tables so that it dynamically expand as you add more regions.

20230126 Nested If or Vlookup Combo Cantrecallmyusername.xlsx
ABCDEFGHIJKLMNOPQ
1Lookup Table
2Space36.5
3EARTH35
4
5ResourceRoleRole#2LeaverJoinerRegionGap1No.3/07/202210/07/202217/07/202224/07/202231/07/2022
6Donald DuckDuckDuckNoSpace136.536.536.536.536.5
7Bugs BunnyBunnyBunny10/07/2022Space136.50000
8PlutoDogDogY17/07/2022EARTH100353535
Modified
Cell Formulas
RangeFormula
M6:Q8M6=LET(dtStart, MIN( FILTER( $5:$5, ISNUMBER($5:$5) ) ), dtUseJoin, IF(ISNUMBER($H6), $H6, dtStart-1), dtLast, MAX( FILTER( $5:$5, ISNUMBER($5:$5) ) ), dtUseLeave, IF(ISNUMBER($F6), $F6, dtLast+1), hrsToUse, XLOOKUP($J6, $L$2:$L$3, $M$2:$M$3, 0), multiplier, $L6, IF( AND(dtUseJoin <= M$5, dtUseLeave > M$5),hrsToUse * multiplier,0))
Click to expand...
Thank you for this - it worked in the manner I wished. I dont fully understand all the formulas currently but will look into this some more in due course.
I will not need a table as I will only ever have 2 options for my regions though appreciate the comment - if there were more variables it would make perfect sense ;-)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Romano_odK
  • Solved
How to add that percentage in column H.
2
Replies
17
Views
735
PeteWright
PeteWright
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
Romano_odK
  • Question Question
IFS formula doesn't work
Replies
4
Views
603
Peter_SSs
Peter_SSs
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
75
Sergius
S
Romano_odK
  • Solved
How to remove al line with VBA when the values is "OK"
Replies
9
Views
444
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

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