#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
The following formula in H5 works:
Code:
=MAX(I$4-B5,0) |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Nested IF | TDWolsey | Excel | 2 | 01-06-2016 11:31 PM |
nested if and averageA | windseaker | Excel | 0 | 08-14-2015 04:13 PM |
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 IFs | JimS378 | Excel | 7 | 05-03-2011 08:20 PM |