Formula Help

[Peter]

This has got me really scratching my head.

I have the following formula in a table:

=IF(B26>=25,"",IF(G5>=G50,"YES",""))

The problem is thatt B26 is less than 25 (8), and so the answer comes
up blank. But if I change the formula to:

=IF(B26<=25,"",IF(G5>=G50,"YES",""))

Then the cell will say, "YES."

What am I doing wrong?

 

[Pete_UK]

Well, what do you want to happen if B26 is less than 25?

Pete

 

[Peter]

If B26 is <25, then the word YES will show up in the cell.

 

[joeu2004]

If B26 is <25, then the word YES will show up in the cell.

What you wrote previously is effectively:

=if(and(B26<25,G25>=50), "YES", "")

Are you trying to write:

=if(or(B26<25,G25>=50), "YES", "")


----- previous posting -----

 

[joeu2004]

PS....

if I change the formula to:
=IF(B26<=25,"",IF(G5>=G50,"YES",""))

Then the cell will say, "YES." What am I doing wrong?

If that is what you want, then perhaps you made only two mistakes in
your second effort. Perhaps you wanted the following:

=if(B26<25, "YES", if(G5>=G50, "YES", ""))

Note: "<25", not "<=25".

Generally, that is equivalent to the IF(OR(...),...) formula that I
suggested. Arguably, one advantage of your second formulation is that
it will return "YES" if B26 is less than 25, even if G5 contains an
error.

I would prefer to avoid any hypothetical errors in G5. But to each
his own.

 

[Peter]

What I want is if B26 is greater than or equal to 25 then nothing
appears in the cell. Then if G5 is greater than or equal to G50, then
I want the word YES to appear in the cell.

Note: "<25", not "<=25".

Isn't <= how you write "less than or equal to?"

 

[joeu2004]

What I want is if B26 is greater than or equal to 25 then nothing
appears in the cell. Then if G5 is greater than or equal to G50, then
I want the word YES to appear in the cell.

Do you mean that you want "" when B26>=25, regardless of the value in
G5; otherwise (when B26<25) you "YES" when G5>=G50; and otherwise
(when B26<25 and G5<G50) you want ""?

If so, that is what your original formula does. But you said that is
not giving the desired result when B26<25. In response to Pete-UK,
you said: "If B26 is <25, then the word YES will show up in the
cell". I interpreted "will" to mean that is what you want; that is,
you want "YES" when B26<25 as well as when G5>G50.

Before you can write an IF expression correctly, you need to fully
specify your requirements, taking all combination of conditions into
account. English description can be ambiguous. One way to do that is
to fill out the following table (sorry for any alignment problems):

G5<G50 G5=G50 B5>G50
------------ ------------- -----------
B26<25 xxx xxx xxx
B26=25 xxx xxx xxx
B26>25 xxx xxx xxx

Replace "xxx" with the desired result, "YES" or "". Some of those
combination might become redundant and reducible after you fill in the
table.

Isn't <= how you write "less than or equal to?"

Yes. But in your original posting, you wrote that the problem arises
when "B26 is less than 25". "Less than" (<) is the opposite of
"greater than or equal" (>=), not "less than or equal" (<=).

 

[David Biddulph]

That's what the first formula of your original message does:
=IF(B26>=25,"",IF(G5>=G50,"YES",""))

What is your problem?
What are your values in B26, in G5, and in G50? What result does it give,
and what result did you expect?
Note that you haven't specified in your words below what you want if B26<25
and G5<G50, and the formula gives an empty string. If you want something
different, then
=IF(B26>=25,"",IF(G5>=G50,"YES","whatever alternative result you want"))
--
David Biddulph

What I want is if B26 is greater than or equal to 25 then nothing
appears in the cell. Then if G5 is greater than or equal to G50, then
I want the word YES to appear in the cell.

....

 

[RF]

Thank you. The first formula I posted worked fine. I was having some
other problems, fixed those, and then that very same formula would no
longer work.. So, out of frustration I reversed the B26>=25 to
B26<=25 to see what would happen, and it worked. I had no idea what
was going on.

The value in B26 is 8.
The value in G5 is 58.
The value in G50 is 25.

If G5 is greater than G50 I want the cell in G26 to say "YES."

But if B6 is >= 25, and even though G5 is greater than G50, I want the
cell to be blank.

This is how the formula should look and it should work, but it doens't
work now unless I reverse the B26>=25 to B26<=25, and I cannot fathom
why.

=IF(B26>=25,"",IF(G5>=G50,"YES",""))

The above formula is the one I'm working with. Forget about the
B26<=25 stuff. This is what I'm trying to solve. It was working, it
should work, but after hours of playing with it I give up.

Thanks for your help.

 

[joeu2004]

If you are still looking for assistance, I suggest that you cut-and-
paste the formula from the Excel into your posting.

I suspect that instead of "B6>=25", it now reads "26>=25 or "86>=25"
or some other typo that can easily be misread as "B6>=25" because that
is what you expect.

At least, that is one kind of "stoopid" mistake I discover I have made
when things no longer work inexplicably. We tend to read what we
expect, not always what is there before us.


----- original posting -----

 

[Peter]

Here is the formula I copied from a table that worked.

=IF(D48="","",IF($B$24>=25,"",IF(D3>=D48,"YES","")))

The same formula in my new table doesn't work.

 

[David Biddulph]

So tell us what values you have in D48, B24, and D3, what value the formula
returns, and what you expected it to return.
[Hint: If you want help, it's no good just saying "it doesn't work".]

 

[Peter]

Sorry guys, I appreciate your help, but I'm so frustrated and confused
I'm having trouble explaining things properly. I'll try one more
time.

I have a 16x16 table. Here are the formulas for one entire column.

=IF(H48="","",IF($B$24>=25,"",IF(H3>=H48,"YES","")))
=IF(H49="","",IF($B$25>=25,"",IF(H4>=H49,"YES","")))
=IF(H50="","",IF($B$26>=25,"",IF(H5>=H50,"YES","")))
=IF(H51="","",IF($B$27>=25,"",IF(H6>=H51,"YES","")))
=IF(H52="","",IF($B$28>=25,"",IF(H7>=H52,"YES","")))
=IF(H53="","",IF($B$29>=25,"",IF(H8>=H53,"YES","")))
=IF(H54="","",IF($B$30>=25,"",IF(H9>=H54,"YES","")))
=IF(H55="","",IF($B$31>=25,"",IF(H10>=H55,"YES","")))
=IF(H56="","",IF($B$32>=25,"",IF(H11>=H56,"YES","")))
=IF(H57="","",IF($B$33>=25,"",IF(H12>=H57,"YES","")))
=IF(H58="","",IF($B$34>=25,"",IF(H13>=H58,"YES","")))
=IF(H59="","",IF($B$35>=25,"",IF(H14>=H59,"YES","")))
=IF(H60="","",IF($B$36>=25,"",IF(H15>=H60,"YES","")))
=IF(H61="","",IF($B$37>=25,"",IF(H16>=H61,"YES","")))
=IF(H62="","",IF($B$38>=25,"",IF(H17>=H62,"YES","")))
=IF(H63="","",IF($B$39>=25,"",IF(H18>=H63,"YES","")))

Here are the values for H48:H63

16
49
816
148
FALSE
288
288
816
90
577
288
#VALUE! (Value because of deleted data replaced by - - -)
14
no value (blank)
no value (blank)
no value (blank)

These are the values for H3:H18

33
63
942
233
X (An x represents #4 with #4 for which there can be no data)
404
403
1,117
117
710
439
---
24
no value
no value
no value

And the values for B:24 through B:29 are:

2
6
99
18
4.5
35
35
99
11
70
35
#VALUE! (Data deleted replaced with three dashes - - -)
1.8
0.0
0.0
0.0


Thanks very much for all you're help. I really do appreciate it.

 

[David Biddulph]

Sorry, enough's enough. Every time you come back with only half a story,
and it's different from what you've talked about before.

This time round you've told us input values for a variety of different
formulae from the ones you've quoted before, but you *still* haven't told us
what unexpected output results you're getting from even one of those
formulae. If you would stick to *one* formula, and read what you've been
asked before, namely that to help you we need to know the formula, the input
values, the output you got, and the output you expected, then it is possible
that someone with more patience than I have (or better extra-sensory powers
to guess what you're doing) might be prepared to try to help.

[And if someone else is going to try to help, they'll need to see what you
wanted your formula to do, so it doesn't make much sense to snip that out of
the message.]
--
David Biddulph

Sorry guys, I appreciate your help, but I'm so frustrated and confused
I'm having trouble explaining things properly. I'll try one more
time.

I have a 16x16 table. Here are the formulas for one entire column.

=IF(H48="","",IF($B$24>=25,"",IF(H3>=H48,"YES","")))
=IF(H49="","",IF($B$25>=25,"",IF(H4>=H49,"YES","")))
=IF(H50="","",IF($B$26>=25,"",IF(H5>=H50,"YES","")))
=IF(H51="","",IF($B$27>=25,"",IF(H6>=H51,"YES","")))
=IF(H52="","",IF($B$28>=25,"",IF(H7>=H52,"YES","")))
=IF(H53="","",IF($B$29>=25,"",IF(H8>=H53,"YES","")))
=IF(H54="","",IF($B$30>=25,"",IF(H9>=H54,"YES","")))
=IF(H55="","",IF($B$31>=25,"",IF(H10>=H55,"YES","")))
=IF(H56="","",IF($B$32>=25,"",IF(H11>=H56,"YES","")))
=IF(H57="","",IF($B$33>=25,"",IF(H12>=H57,"YES","")))
=IF(H58="","",IF($B$34>=25,"",IF(H13>=H58,"YES","")))
=IF(H59="","",IF($B$35>=25,"",IF(H14>=H59,"YES","")))
=IF(H60="","",IF($B$36>=25,"",IF(H15>=H60,"YES","")))
=IF(H61="","",IF($B$37>=25,"",IF(H16>=H61,"YES","")))
=IF(H62="","",IF($B$38>=25,"",IF(H17>=H62,"YES","")))
=IF(H63="","",IF($B$39>=25,"",IF(H18>=H63,"YES","")))

Here are the values for H48:H63

16
49
816
148
FALSE
288
288
816
90
577
288
#VALUE! (Value because of deleted data replaced by - - -)
14
no value (blank)
no value (blank)
no value (blank)

These are the values for H3:H18

33
63
942
233
X (An x represents #4 with #4 for which there can be no data)
404
403
1,117
117
710
439
---
24
no value
no value
no value

And the values for B:24 through B:29 are:

2
6
99
18
4.5
35
35
99
11
70
35
#VALUE! (Data deleted replaced with three dashes - - -)
1.8
0.0
0.0
0.0


Thanks very much for all you're help. I really do appreciate it.

So tell us what values you have in D48, B24, and D3, what value the
formula
returns, and what you expected it to return.
[Hint: If you want help, it's no good just saying "it doesn't work".]