Having trouble with IF statements.

[RigbyDigby]

I am having trouble getting a formula to work for converting an old excel data file to our new excel data template. Basically if the cell does not equal one of these five words I want it to be blank, if it does equal one of these five words I want it to just flow through to new excel. (Poor, Fair, Average, Good, and Excellent).

I am also having trouble with figuring out a way to put a formula together so that if the cell i'm calling upon is greater than 100 it will be blank. If the cell is between 0 and 100 or 0% to 100% it will just flow through to the new cell location.

Sorry if I do not have enough context but I have included an excel with how I want the tables to look

[joeu2004]

(EDIT: Note errata in red.)

E3: =IF(OR(D3={"poor","fair","average","good","excellent"}), D3, "")

For B3, your requirements are ambiguous. We cannot distinguish between 2 (which is okay because it is between 0 and 100) and 200% (which is not okay because it is not between 0% and 100%).

[RigbyDigby]

Thanks for the help on the first one. To clear up the issue with B3. I may have explained it wrong. I basically only want percentages to transfer to the B Column. So any number that isnt labeled a percentage should not transfer over and I am having trouble figuring it out.

[RigbyDigby]

I want the formula to grab from the A2 cell and if the number is between 0 and 100 to follow through as a percentage to B2. As the data set I am pulling from has the A2 cells labeled as percentages but are categorized under the general category.

[joeu2004]

Well, what appears to be percentages in column A are actually text; the other values in the column are bona fide numbers formatted to left.

With that peculiarity, the following is sufficient in B3:

=IFERROR(IF(AND(RIGHT(A3,1)="%", 0<=--A3, --A3<=1), A3, ""), "")

The IFERROR covers the case (not in your file) where the rightmost character is "%", but Excel does not recognize the string as numeric.

The double negate ("--") effective converts numeric text to a number. (Of course, it also works with numeric values.)

In that case, B3 is text, just like A3. If you want a numeric value, replace the last A3 with --A3, and format B3 as Percentage.

However, that would not work with bona fide percentages, where are just like any other number except for the Percentage format.

If you want to cover that case as well, use the following in B3:

=IFERROR(IF(OR(LEFT(CELL("format",A3),1)="p", AND(RIGHT(A3,1)="%", 0<=--A3, --A3<=1)), A3, ""), "")

formatted as Percentage. B3 is text or numeric, depending on A3. Again, you can replace the last A3 with --A3.