Formula gives unwanted result

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jeannie v - 05 Mar 2008 15:30 GMT

Hi Experts:

I've poured over the other questions and responses on this site and either
missed my situation or it's not there...I need help with what should be so
simple...

Example: B13 is 20...I want C13 to give me the result of 3 of B13 is
greater than 17....I then need the formula to say...If B13 is not greater
than 17, enter 0.

This formula that I've started returns the result of 1 not 3...I then need
to expand the formula to enter zero if B13 is not greater than 17. The
result would be returned in C13.

=SUM($B13>17)

I know this is really simple, so be kind....my brain is fried....

Thank you so much for all your expertise.....

Signature

jeannie v

Reply to this Message

PCLIVE - 05 Mar 2008 15:42 GMT

Maybe this is what you want:

=IF(B13>17,3,0)

HTH,
Paul

> Hi Experts:
>
[quoted text clipped - 15 lines]
>
> Thank you so much for all your expertise.....

Reply to this Message

jeannie v - 05 Mar 2008 15:53 GMT

Hi PCLIVE:

OMG...this wa so easy.....it works...I've wasted so much time trying to
figure it out...I can't thank you enough for helping me!!!!!

Signature

jeannie v

> Maybe this is what you want:
>
[quoted text clipped - 22 lines]
> >
> > Thank you so much for all your expertise.....

Reply to this Message

jeannie v - 05 Mar 2008 15:55 GMT

Hi Paul:

OK...now if the 3 is variable, how would I replace the 3 so that whatever
the result is it will be returned?

Thank you for all your help,

Signature

jeannie v

> Maybe this is what you want:
>
[quoted text clipped - 22 lines]
> >
> > Thank you so much for all your expertise.....

Reply to this Message

PCLIVE - 05 Mar 2008 16:03 GMT

Assuming your 3 (variable) will be in A1:

=IF(B13>17,A1,0)

Adjust "A1" to the cell containing 3 or your variable. I'm not sure if that
is what you were saying. If not, please elaborate.

Regards,
Paul

> Hi Paul:
>
[quoted text clipped - 33 lines]
>> >
>> > Thank you so much for all your expertise.....

Reply to this Message

RagDyeR - 05 Mar 2008 16:04 GMT

You can put your variable value in a particular cell, and have the formula
refer to that cell so that all you need to do is change the value in that
cell.

Say you use A1 for the value, try this:

=(B13>17)*A1

Signature

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi Paul:

OK...now if the 3 is variable, how would I replace the 3 so that whatever
the result is it will be returned?

Thank you for all your help,

Signature

jeannie v

"PCLIVE" wrote:

> Maybe this is what you want:
>
[quoted text clipped - 26 lines]
> >
> > Thank you so much for all your expertise.....

Reply to this Message

Ron Rosenfeld - 05 Mar 2008 15:51 GMT

>Hi Experts:
>
[quoted text clipped - 15 lines]
>
>Thank you so much for all your expertise.....

Look closely at your formula:

B13>17 is a Boolean expression that will return TRUE or FALSE depending.

Converting it to a numeric value, as your are doing using the SUM function,
converts TRUE to 1 and FALSE to 0.

So something like:

C13: =IF(B13>20,"undefined",IF(B13=20,3,IF(B13>17,"undefined","ENTER 0")))

--ron

Reply to this Message

jeannie v - 05 Mar 2008 16:02 GMT

Hi Ron:

Thank you for your answer and it so appreciated!!!! I've tried RagDyer's
answer and it works perfectly.....I will use your formula for another
application that I'm working on...so I can't thank you enough!

Signature

jeannie v

> >Hi Experts:
> >
[quoted text clipped - 28 lines]
>
> --ron

Reply to this Message

RagDyeR - 05 Mar 2008 15:52 GMT

You didn't say what to do if B13 EQUALS 17.

This returns 3 if B13 is greater then 17
AND
0 if B13 is less then OR equal to 17:

=(B13>17)*3

Signature

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Signature

jeannie v

Reply to this Message

jeannie v - 05 Mar 2008 16:04 GMT

HiRagDyeR:

Well...you have given me the formula that works for the entire worksheet and
I am so appreciative. Thank you...you have relieved me of that stress you
experience when you keep trying different methods over and over and just get
so frustrated.

I thank you for your expertise!

Signature

jeannie v

> You didn't say what to do if B13 EQUALS 17.
>
[quoted text clipped - 23 lines]
>
> Thank you so much for all your expertise.....

Reply to this Message