How could I set this spreadsheet up

[Psilver1]

I have a spreadsheet I am trying to do which is for an experiment but I have had little luck coming up with a good way to do it. Basically I have one massive category which is Natural Habitat. In the experiment the area of all habitats are 2 sq miles. Data was collected over the course of a few week period and the measurements which are in sq meters for Natural habitat is listed in column A. What I want to show is that for each measurement in column A, that specific area was made up of either Ground Level Vegetation, Forests or Miscellaneous. I want to be able to run an Anova off of this info. So for example in Cell A2 the measurement was 890 meters squared. Cell B2, C2, or D2 would be checked or marked somehow to show that is was primarily Ground Level Veg, Forrest or Misc. I have tried using text in these areas and I can't run any statistics on "text" if I use the #1, 2 and 3 it throws of the statistics because you are basically saying there is one of that thing within that area. The list in column A is 2050 rows, and is randomly generated values. It would be okay if 2 things occurred at the same time. S0 you could have a 890sq meter area which consists of GLV and Forrest. Just an fyi I am saying that if the area is 50% or more of one thing than the other 2 than it is named the thing. Hopefully this makes sense, I know what I am looking for just don't know how to get it.

[JBeaucaire]

There's no reason you can't run statistical tests on cells with text in them. You just need to be clear WHAT you're counting.

Create a small excerpt 20-row version of your workbook. Show us the data in column A and the "x" markers in B:D for those 20 rows. Then mock up manually the kinds of statistical results you would want to see from those 20 rows.

20 rows is small enough you can work out the statistics manually and mock them up unto a report to show us what you're after. We can help convert that into formulas that would work over the entire 2050 rows of real data.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[Psilver1]

Ok I have attached a small section of what I have so far. If you notice there are 2 habitats and then measurements taken from each habitat. I have calculated the density of ant mounds per measurement within the habitat. What I am looking for now is do the symbols, triangle, diamond for Natural and then triangle, diamond, square for Landscaped. Do these symbols (in reality the symbol desribes a characteristic) but do they play a role in the density of ant mounds over the entire habitat not just for the specific measurement. Also looking to see within each habitat are there more triangles or diamonds for Natural and the other 3 for Landscaped. These would be in column charts where I could run a A nova. Sorry if I am confusing, kind of hard to describe.

[JBeaucaire]

I don't want to understand the data, I want to help you convert a statistic you've demonstrated into a formula. You've posted a sample of data, but you left out the manually created mockup of the desired results. 20 rows is meant to make it easier for you to calculate a result, mock it up, then explain it if it's not abundantly clear.

Slim down to 10 rows if needed.

[Psilver1]

Thats where I am struggling. If I wanted to show the total number of triangles in Column C to the Total Area in Column A how could I compute that. Because a triangle is not a number value. Obviously by hand over 20 rows but over 2050 rows x 10 different spreadsheets is not realistic. So basically cell A2 cooresponds with the diamond in cell C2 but since all of the values in column A are different each triangle or diamond would represent a different value. In the end for example it would show there are 10000 sq meters of triangles over the 50000 sq meter area and there are 40000 sq meters of diamonds over the 500000 sq meter area. And those totals could go into a column chart.

[JBeaucaire]

They aren't triangles. They are the letter "t" in a font that looks like a triangle.

You can use a COUNTIF to count how many cells have a "t"

=COUNTIF(C:C, "t")

[Psilver1]

Awesome, last thing before I get this thing put together for a good review. Is there a better way to show a shape. I like the idea of squares, triangles and diamonds but I do see the "t". Id much rather have a shape without the "t", Thanks you so much for your help

[JBeaucaire]

No, shape fonts are a common technique. I use the marlett font all the time to show a "checkmark" in a cell (the letter 'a'), sometimes it just looks cooler.

[Psilver1]

Is it possible to write a formula where you have say Cell B2,C2,D2 and you set a limit on their summed value but the values in the cells are random numbers. Basically Excel would put numbers in the cells that when added together they wouldn't be greater than a certain number.

[JBeaucaire]

How about:

=MIN(100, SUM(B2:C2))