My IF in Excel formula is not returning the correct value.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steph - 17 Aug 2006 22:58 GMT

I am doing a reference of 1, 2, or 3 returning specific text based on the
number. Here is my formula.

=IF(G29=1,"DOES NOT MEET",IF(G29=2,"CONSISTENTLY MEETS",IF(G29=3,"EXCEEDS")))

if the ratings are all 3's the correct text value will appear. If you vary
the ratings I receive a false value. Can anyone help me.

Reply to this Message

Franz Verga - 17 Aug 2006 23:03 GMT

> I am doing a reference of 1, 2, or 3 returning specific text based on
> the number. Here is my formula.
[quoted text clipped - 4 lines]
> if the ratings are all 3's the correct text value will appear. If
> you vary the ratings I receive a false value. Can anyone help me.

Hi Steph,

I'm not sure to have well understood, but try this:

=IF(G29=1,"DOES NOT MEET",IF(G29=2,"CONSISTENTLY
MEETS",IF(G29=3,"EXCEEDS","")))

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Paul B - 17 Aug 2006 23:05 GMT

Steph, try this,

=IF(G29=1,"DOES NOT MEET",IF(G29=2,"CONSISTENTLY
MEETS",IF(G29=3,"EXCEEDS","")))

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> I am doing a reference of 1, 2, or 3 returning specific text based on the
> number. Here is my formula.
[quoted text clipped - 3 lines]
> if the ratings are all 3's the correct text value will appear. If you vary
> the ratings I receive a false value. Can anyone help me.

Reply to this Message

MyVeryOwnSelf - 17 Aug 2006 23:10 GMT

> I am doing a reference of 1, 2, or 3 returning specific text based on
> the number. Here is my formula.
[quoted text clipped - 4 lines]
> if the ratings are all 3's the correct text value will appear. If you
> vary the ratings I receive a false value. Can anyone help me.

I tried your formula, and get (what seems to be) the correct result if G29
is either 1, 2, or 3.

If G29 is any other value, I get FALSE because the formula doesn't provide
for a result in that case.

If you change the formula to provide for other values
=IF(G29=1,"DOES NOT MEET",
IF(G29=2,"CONSISTENTLY MEETS",IF(G29=3,"EXCEEDS","other")))
it should become clear what's going on.

Reply to this Message

Steph - 18 Aug 2006 00:04 GMT

Thank you Paul that helped some what. I am still having a problem. Picture
a performance review with a row of 1,2,3 as the points for each cell. At the
end the points are all added up and an overall rating is calculated 1,2, or
3. If I key in all 1 the value = does not meet, however if I populate the
fields 1, 2, or 3 randomly the value is false or blank with what you
proposed. But I was thinking it should not matter what get's me to the cell
that I am referencing as long as it equals 1,2,3. Your thoughts?

Reply to this Message

Bearacade - 18 Aug 2006 00:21 GMT

Whats the formula in G29?

Signature

Bearacade

Reply to this Message

Steph - 18 Aug 2006 03:14 GMT

This is the formula I have in the cell so it should work right?

=IF(G29=1,"DOES NOT MEET",IF(G29=2,"CONSISTENTLY
MEETS",IF(G29=3,"EXCEEDS","")))

> Whats the formula in G29?

Reply to this Message

Paul B - 18 Aug 2006 01:00 GMT

Steph, not sure what you are doing, I can put =A1+A2+A3 in cell G29, or
something else and as long as the formula comes up with 1,2,or 3 the message
is displayed. As Bearacade asked what is in cell G29, and maybe how it is
formatted, could be showing 1,2,or 3 but the answer may be something like
2.1?

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Thank you Paul that helped some what. I am still having a problem. Picture
> a performance review with a row of 1,2,3 as the points for each cell. At the
[quoted text clipped - 11 lines]
> > if the ratings are all 3's the correct text value will appear. If you vary
> > the ratings I receive a false value. Can anyone help me.

Reply to this Message

Steph - 18 Aug 2006 03:12 GMT

Service Category                        Rating
Did the associate you spoke with provide great service?        1
How satisfied are you with the purchase you made? 2
How satisfied are you with the transaction process?         3
How likely are you to buy from us again?                 2
How likely are you to recommend our company to others?     1

Total Points                      2
Performance Rating
*

*this value should display Consistently Meets but is blank, if they points
are all 2 then the value displays consistently meets. It shouldn't matter
though since my IF formula is based of the total points of 1,2,3. I did try
percision as displayed and now it works but I can not format anything.

> Steph, not sure what you are doing, I can put =A1+A2+A3 in cell G29, or
> something else and as long as the formula comes up with 1,2,or 3 the message
[quoted text clipped - 24 lines]
> vary
> > > the ratings I receive a false value. Can anyone help me.

Reply to this Message

Paul B - 18 Aug 2006 04:51 GMT

Steph, you can't have that formula in G29, it would be a circular reference,
the cell reference refers the formula's results, you would need to have the
formula in another cell and the total points formula in G29, what formula
are you using to get the total points from the numbers 1,2,3,2,1?

So what cell is the formula in?
And what cell is the formula for total points in? and what is that formula?

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Service Category Rating
> Did the associate you spoke with provide great service? 1
[quoted text clipped - 40 lines]
> > vary
> > > > the ratings I receive a false value. Can anyone help me.

Reply to this Message

MyVeryOwnSelf - 18 Aug 2006 18:29 GMT

> Picture a performance review with a row of 1,2,3 as the points for
> each cell. At the end the points are all added up and an overall
[quoted text clipped - 3 lines]
> should not matter what get's me to the cell that I am referencing as
> long as it equals 1,2,3. Your thoughts?

Maybe the CSV file below would help.

--------------------- cut here --------------------------
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
Service Category,,,,,,Rating
Did the associate you spoke with provide great service?,,,,,,3
How satisfied are you with the purchase you made?,,,,,,3
How satisfied are you with the transaction process?,,,,,,2
How likely are you to buy from us again?,,,,,,2
How likely are you to recommend our company to others?,,,,,,2
,,,,,,
Total Points,,,,,,"=ROUND(AVERAGE(G23:G28),0)"
Performance Rating,,,,,,"=IF(G29=1,""DOES NOT MEET"", IF(G29=
2,""CONSISTENTLY MEETS"",IF(G29=3,""EXCEEDS"",""other"")))"

Reply to this Message