"RIGHT" extracted value not returning correct result within IF statement

**PlutoISaPlanet** · 11-05-2012, 02:51 PM

This is my IF statement:
=IF(RIGHT(G1)=Holding_Period,SUM(G19:G22),G14)
h8cuu.png

And my RIGHT Statement:
Y6YRd.png

My Holding_Period name equals 5 and in the screenshot of the IF statement the logical test should be TRUE. I'm not sure why it's not coming out that way... ?

**Miraun** · 11-05-2012, 02:54 PM

Is Holding Period a named range? I can't tell if you're using the right function properly either based upon what you've attached. Did you also mean to use RIGHT on the Holding_period range?

We would need a sample workbook with more information to give you a better answer...

**Pauleyb** · 11-05-2012, 03:10 PM

Probably because RIGHT returns a string of '5' and your Holding_Period is an integer. Try:
=IF(--RIGHT(G1)=Holding_Period,SUM(G19:G22),G14)

**PlutoISaPlanet** · 11-05-2012, 03:25 PM

That did it Pauley. I don't understand what the --'s did, could you tell me?
Thanks a million.

**Pauleyb** · 11-05-2012, 03:58 PM

If you do a mathematical operator on a text string that CAN be a number, then Excel changes the string to a number. The '--' is a double negation, so it returns the appropriate number. You could have also used '1*' or '0+' but the '--' seems to be the favored method.

**PlutoISaPlanet** · 11-05-2012, 04:02 PM

I see. It's similar to a problem I run into with another program I use often that won't calculate inconsistent units. In that case I divide by 1 of the same unit to achieve the calculation. Thanks again.