Articles

Solved What is the term to use in an IF formula.....

January 23, 2013 at 09:10:29
Specs: Windows 7

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

See More: What is the term to use in an IF formula.....

Report •


#1
January 23, 2013 at 09:16:06
✔ Best Answer

If P3 have the actual symbol ####
then something like:

=IF(P3="####",TRUE,FALSE)

MIKE

http://www.skeptic.com/


Report •

#2
January 23, 2013 at 09:32:52

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

Report •

#3
January 23, 2013 at 14:06:53

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, 0

The 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.


Report •

Related Solutions

#4
January 23, 2013 at 14:49:07

Try this:

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

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


MIKE

http://www.skeptic.com/


Report •

#5
January 23, 2013 at 15:22:57

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.


Report •

#6
January 23, 2013 at 15:24:22

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.


Report •

#7
January 23, 2013 at 15:35:29

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?

Report •

#8
January 23, 2013 at 15:40:39

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

http://www.skeptic.com/


Report •

#9
January 23, 2013 at 15:45:35

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

Report •

#10
January 23, 2013 at 15:59:14

but the $5 fee did not populate in the row with the hashmarks

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.

If you want to explain what it is your trying to do
we might be able to help.

MIKE

http://www.skeptic.com/


Report •

#11
January 23, 2013 at 15:59:45

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


Report •

#12
January 23, 2013 at 16:09:03

"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


Report •

#13
January 23, 2013 at 16:09:15

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

http://www.skeptic.com/


Report •

#14
January 23, 2013 at 16:13:33

=IF(OR(M13="####",M13-J13>9),5,0)

is the actual formula. and the previous statement is incorrect-- the fx is in O13


Report •

#15
January 23, 2013 at 16:17:11

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.

Report •

#16
January 23, 2013 at 16:21:46

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

http://www.skeptic.com/


Report •

#17
January 23, 2013 at 16:23:51

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

Report •

#18
January 23, 2013 at 16:28:52

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

Report •


Ask Question