Microsoft Office Forums

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, 15 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: 837
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,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

That could be solved with =IF(or( TYPE(B5)>1,B5=0),"",MAX(I$4-B5,0))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:46 PM.


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