Trouble with Formula

[LzzygrlCharmed]

Below is a formula I am trying to use in cell E206. I am trying to have the formula fill in E206 with either Common, Plentiful, Limited, Rare, Scarce based on the Price in Column F. I am trying to have the formula reference the cells that say 0-10, 10-19.99, 20-29.99, 30-39.99, 40-1,000 as the rule. Example: if the price falls in between 0-10 it will show the word Common in cell E206.

Forumula: =IF(F206=$J$212,"Common",IF(F206=$J$213,"Plentiful",IF(F206=$J$214,"Limited",IF(F206=$J$215,"Rare",(IF(F206=$J$216,"Scarce","ERROR"))))))

[TABLE="width: 522"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Example of Spreadsheet:[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] $ - [/TD]
[TD="align: right"]100[/TD]
[TD] $ - [/TD]
[TD] $-[/TD]
[TD]0-10[/TD]
[/TR]
[TR]
[TD]Common[/TD]
[TD] $ 9.25[/TD]
[TD="align: right"]100[/TD]
[TD] $ 0.0000013504[/TD]
[TD] $ 1.99[/TD]
[TD]10-19.99[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] $ - [/TD]
[TD="align: right"]100[/TD]
[TD] $ - [/TD]
[TD] $- [/TD]
[TD]20-29.99[/TD]
[/TR]
[TR]
[TD]Common[/TD]
[TD] $ 3.06[/TD]
[TD="align: right"]100[/TD]
[TD] $ 0.0000318750[/TD]
[TD] $ 1.99[/TD]
[TD]30-39.99[/TD]
[/TR]
[TR]
[TD]Plentiful[/TD]
[TD] $ 12.14[/TD]
[TD="align: right"]100[/TD]
[TD] $ 0.0000119020[/TD]
[TD] $ 2.99[/TD]
[TD]40-1,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrmmickle1]

You can complete this task using a regular Vlookup. You just need to set up the reference table correctly:

Here is a simple example:
http://blog.contextures.com/archives/2009/06/01/convert-percentages-to-letter-grades-with-vlookup/

 

[Eric W]

I'd probably rearrange things a little. Somewhere on this sheet put these 2 columns:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]0[/TD]
[TD]Common[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Plentiful[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Limited[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Rare[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Scarce[/TD]
[/TR]
</tbody>[/TABLE]

You could put this on another sheet, but let's call them Y1:Z5. The left column is the lower bound for the range, and the right is the description. Given that, you could use this function based on the price:

=LOOKUP(C5,$Y$1:$Z$5)

where C5 is the price. And that's it.