Microsoft Office Forums nested formula using IF & AND
 Register FAQ Search Today's Posts Mark Forums Read

#1
02-17-2016, 09:30 AM
 paxon Windows XP Office 2007 Novice Join Date: Feb 2016 Posts: 13
nested formula using IF & AND

I would like to know if it is possible to make a nested formula with IF & AND with the following logic:
If the difference between two dates is negative (column “I”) give me 0; otherwise if the difference is positive (column “j”) and the range of cell is = to OPEX then do the calculation (OPEX/365*nr.of days)
You can see the attached file as example. (the yellow cells are the one that formula is not correct, I want 0 value because number of days is negative.

Thanks
Attached Files
 Book1.xlsx (9.8 KB, 9 views)
#2
02-17-2016, 10:16 AM
 gebobs Windows 7 64bit Office 2010 64bit Expert Join Date: Mar 2014 Location: Atlanta Posts: 832

The following formula in H5 works:
Code:
`=MAX(I\$4-B5,0)`
#3
02-18-2016, 02:38 AM
 paxon Windows XP Office 2007 Novice Join Date: Feb 2016 Posts: 13

Although this solves partially the problem because in same cases instead of a date as completion date there is a text (like date not defined) and I get #VALUE!.
If you know a solution that would be great, otherwise thanks for your help
#4
02-18-2016, 05:32 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,397

That could be solved with =IF(or( TYPE(B5)>1,B5=0),"",MAX(I\$4-B5,0))
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post TDWolsey Excel 2 01-06-2016 11:31 PM windseaker Excel 0 08-14-2015 04:13 PM wentworth16238 Excel 5 06-14-2015 12:59 PM asluder2000 Excel 2 11-14-2014 11:00 PM JimS378 Excel 7 05-03-2011 08:20 PM

All times are GMT -7. The time now is 09:55 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top