Formula with IF, TODAY and MAX not working.

[reberryjr]

I'm not sure why this formula isn't working. If AZ2 = "", then I want to return the value of 0. If it isn't null, I want to get the number of days between Today and the max date in column AZ.

=IF(AZ2="",0,TODAY()-(MAX(Financials!AZ:AZ)))

When I enter the formula on the Financials sheet, it works fine (obviously without the sheet reference).

Thoughts?

 

[reberryjr]

So, in thinking ahead, if the Client selects a service on 1/1/18, and then discontinues that service, but later signs up for it again on 4/1/18, I would want the formula to calculate off of the 4/1/18 date, not the 1/1/18. I'll try that formula you suggested later tonight when I get home from work.

 

[Aladin Akyurek]

So, in thinking ahead, if the Client selects a service on 1/1/18, and then discontinues that service, but later signs up for it again on 4/1/18, I would want the formula to calculate off of the 4/1/18 date, not the 1/1/18. I'll try that formula you suggested later tonight when I get home from work.

Which formula are you using now?

 

[reberryjr]

This one that you provided works great!
=IF(ISNUMBER(MATCH("Active",Financials!AY:AY,0)),TODAY()-MAX(Financials!AZ:AZ),0)

 

[Aladin Akyurek]

This one that you provided works great!
=IF(ISNUMBER(MATCH("Active",Financials!AY:AY,0)),TODAY()-MAX(Financials!AZ:AZ),0)

How do we know the service the client selects?

 

[reberryjr]

The service selections are in different column ranges.

 

[reberryjr]

So, I'm having an issue with a different formula, but the same concept. I want to see the MIN value of column K on the Stats worksheet, as long as that value is < the value in B18 on the Summaries worksheet. I have the same concept in a different column for the MAX value, and that formula works, but the MIN doesn't. Both formulas are array formulas.

For some reason, the MIN formula is cutting off at the < sign when I try to post the formula examples, even when I wrap it in code tags. The only difference between the MIN and MAX formula is that I use the less than sign instead of the greater than, and replace the word MAX with MIN.

[TABLE="width: 239"]
<tbody>[TR]
[TD="align: left"]=MIN(IF(Stats!K:K<b18,min(stats!k:k),0))< td=""></b18,min(stats!k:k),0))<>[/TD]
[/TR]
[TR]
[TD="align: left"]=MAX(IF(Stats!K:K>B18,MAX(Stats!K:K),0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

When posting a formua with < in it, use spaces arount it like this:

J2 < K2

By the way, it would help if you post a small sample and specify what you want to calculate using that sample.

 

[reberryjr]

Yeah, I've uploaded a couple of files in the past, but my laptop started acting funny afterwards, so I'm reluctant to do so again. Essentially, I want to find the MIN value in column K of the Stats sheet, and return that value, as long as it is less than the value in cell B18 on the Summaries sheet. It works with the MAX function, but the MIN function isn't working...it keeps returning 0.

=MIN(IF(Stats!K:K < B18,MIN(Stats!K:K),0))

So, I started fiddling with the formula, and this appears to be working, but I'm not sure why the structure would be different than the MAX IF

=MIN(IF(Stats!K:K < B18,Stats!K:K,0))

 

[Aladin Akyurek]

Here is the list of possibilities which do not require uploading anything: https://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729