IF AND Nested Formula Help

iando99

New Member
Joined
Apr 9, 2012
Messages
16
Hi Guys & Gurls

thanks for reading this, I'm having a few issues with a current spreadsheet I'm working on, its designed to track inspection frequencies, it was all working fine until the client decided they wanted to track two different industry requirements :(

its set out like this

Cell A1 has the asset build date or 'ground zero' if you like - 01/01/1991

Cell H7 has the client requirement for inspection in days - 365 (every year they'll inspect it)

Cell G7 has the industry best practice for inspection in days - 730 (every 2 years they'll inspect it)

Cell O7 has the Certificate of Conformity date in it - 1/1/10 - (date the asset was built)

Cell P7 has the following formula - =IF(H7="",$A$1,MAX(O7,$A$1))

Cell Q7 is where we record the last inspection date if the client has documentation

Cell S7 is where i'm stuck, it currently reads =IF(H7="","",MAX(Q7,P7)+H7) to give me the date they must next inspect the item, however what i need it to do is

IF H7 is blank use G7
IF BOTH H7 & G7 are blank stay blank

if H7 is used the end of the formula needs to read MAX(Q7,P7)+H7)
if G7 is used it needs to read MAX(Q7,P7)+G7)

hope someone can help, I'm not opposed to hidden cells to do part of the equation if that's the best way around it :)

thank you in advance Ian
 

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.
Ian,

If I understand correctly then try...

=IF(AND(H7="",G7=""),"",IF(H7="",MAX(Q7,P7)+G7,MAX(Q7,P7)+H7))

Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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