Microsoft Office Forums nested formula using IF & AND

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-17-2016, 09:30 AM
paxon paxon is offline nested formula using IF & AND Windows XP nested formula using IF & AND Office 2007
Novice
nested formula using IF & AND
 
Join Date: Feb 2016
Posts: 13
paxon is on a distinguished road
Default 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
File Type: xlsx Book1.xlsx (9.8 KB, 9 views)
Reply With Quote
  #2  
Old 02-17-2016, 10:16 AM
gebobs gebobs is offline nested formula using IF & AND Windows 7 64bit nested formula using IF & AND Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

The following formula in H5 works:
Code:
=MAX(I$4-B5,0)
Reply With Quote
  #3  
Old 02-18-2016, 02:38 AM
paxon paxon is offline nested formula using IF & AND Windows XP nested formula using IF & AND Office 2007
Novice
nested formula using IF & AND
 
Join Date: Feb 2016
Posts: 13
paxon is on a distinguished road
Default

Thanks for your answer.
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
Reply With Quote
  #4  
Old 02-18-2016, 05:32 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline nested formula using IF & AND Windows 7 64bit nested formula using IF & AND Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
nested formula using IF & AND Nested IF TDWolsey Excel 2 01-06-2016 11:31 PM
nested if and averageA windseaker Excel 0 08-14-2015 04:13 PM
nested formula using IF & AND using if and nested vlookup wentworth16238 Excel 5 06-14-2015 12:59 PM
Nested Vlookup Help asluder2000 Excel 2 11-14-2014 11:00 PM
nested formula using IF & AND Nested IFs JimS378 Excel 7 05-03-2011 08:20 PM


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft