What is the term to indicate unknown in an IF/OR formula.

EX:

P3 is a date.

P3 has #### in the cell because it is a date in the future that is unknown.

The desired outcome is: If P3 is unknown, use today

If P3 have the actual symbol ####

then something like:=IF(P3="####",TRUE,FALSE)

MIKE

argh!! I didn't put the " in there! Thank you!

JK

I am having a problem getting an acceptable formula to say:

If M3 minus J3 is greater than 9,

or

If M3 ="####"

the result is 5

if false, 0The result is a dollar amount. M3 and J3 are number values (number of days).

In english, if the actual number of days outstanding (M3) minus the approved number of days outstanding (J3) is greater than 9, there is a $5.00 fee. If it's less than 10 days, there is no fee.

Try this: =IF(M3="####",5,IF(M3-J3>9,5,0))

Is there a reason your putting the Hash symbols in the cell?

MIKE

Mike's answer is right but another, possibly more intuitive, way to write the formula is =IF(OR(M3="####",m3-j3>9),5,0)

I find that using the "OR" function makes the intent clearer.

no, if there is a more effective way, I'd rather do that. Sometimes that cell, M3, generates hashmarks because there is no date specified. (the outstanding item is STILL outstanding). Without that OR condition, it doesn't generate a fee if the item is still outstanding, or in other words, has no date in M3.

In English, if the item is still outstanding, it also needs to calculate a $5 fee.

I'm guessing that M3 is calculated and the hashes are actually an error - usually happens if the resulting date would be a negative. Is that the case?

Using the OR does not work. =IF(OR(M3="####",m3-j3>9),5,0)

When Excel tries to calculate the second half of the OR

M3-J3

it throws up the #VALUE error.Without more information on how the values are arrived

at in the M3 and J3 cells there's not much we can do.

MIKE

I tried the =IF(M3="####",5,IF(M3-J3>9,5,0)) formula, and it is valid, but the $5 fee did not populate in the row with the hashmarks

but the $5 fee did not populate in the row with the hashmarksI have no clue what you mean.

The hash marks are in Cell M3

and I have no idea where, on your

spread sheet, you put the formula.If you want to explain what it is your trying to do

we might be able to help.MIKE

Id say it is an error indication. here is the formula that calculates M3: =L3-E3

L3 is the start date

E3 is the close date

if there is no close date, M3 is ####I don't know if this helps

"I have no clue what you mean.

The hash marks are in Cell M3

and I have no idea where, on your

spread sheet, you put the formula"Sorry--M3 is where the formula is. I put it in then pulled it down to subsequent M cells. M3 has a value. I was referring to the first cell that had to use the formula wherein there is no value (####) in M, M13

In M3 try the formula: =IF(L3="",0,L3-E3)

Make sure that cell M3 is formatted as a Number

Then you can use the formula:

=IF(OR(M3=0,M3-J3>9),5,0)

MIKE

=IF(OR(M13="####",M13-J13>9),5,0) is the actual formula. and the previous statement is incorrect-- the fx is in O13

Mike--would that still be applicable given that the calculation is for the O cells? I'll make sure the formulas have the correct row designation for the row they are in.

Just so you know, I sent my crystal ball to have it repaired and it's not back yet,

so from where I'm sitting I can not see what your spreadsheet looks like,

nor do I know what it is your trying to do.You have to be a lot more specific and explain in detail what it is your doing.

Post a short example, but first read this how to:

http://www.computing.net/howtos/sho...

MIKE

That works for all the data i have in there so far! Thank you, JK

great--i looked at the how-to. that will help a whole lot in the future. It's hard to explain these things! Thanks, JK

Ask Your Question

Weekly Poll

Do you think cellular roaming fees are unfair?

Discuss in The Lounge

Poll History