# Rather hard school assignment. Help please!

#### Jeffrey Hendriks

##### New Member
My teacher wants me to solve the following problem, using any means neccesary.

Show the average price of all plants that need "sun", by using a function.

The sheet contains a table with data on plants. One of the columns is "LIGHT", and contains the different lighting values the plant should be exposed to. One other column is "PRICE", containing the price of the plant.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
If you think how an average is calculated, you may want to consider looking at the
=sumif()
and
=countif()
functions in the help file. Each allows for counting/summing based on one criteria.

Could you give a little more detail a sample perhaps?

Sounds as if a sumif might do you.

Could you give a little more detail a sample perhaps?

Sounds as if a sumif might do you.

O.K.

An example

The following columns are in the table, and the following data is in it:

Name Type Flower Leaf Light Ground Price

tyme plant red green sun dry 2,75
tyme plant lila green sun dry 15,65

I need to get the average price of all plants that need "sun" as light. But I don't know how I can select ALL prices from ALL plants EXCEPT the ones that DO NOT have "sun" as a light type.

Hope that was clear enough.

an array formula is what you would want to use:

for example, assume that in your range of data, column E, rows 2 through 20 contains the sun (or no sun) data for each plant, and column G, rows 2 through 20 contains the prices. in that case, this would be your formula:

=AVERAGE(IF(E2:E20="sun",G2:G20))

keep in mind that when you enter the formula you must press ctrl+shift+enter, and not just enter, since this is an array formula, or else this will not work. follow this formula, customizing it for your own needs, and it will give you the average price of just the plants in your data that need 'sun'.

HTH
kevin

The exact ranges are E9:E65 (this contains "sun" or something else) and H9:H65 (this contains the price.

The formula would be:

=AVERAGE(IF(E9:E65="sun";H9:H65))

Just checked it and it worked. Thanks a lot.
You really helped me out. (Never thought it would be this easy, he he)

With kind regards,

Jeffrey

On 2002-09-26 11:10, Jeffrey Hendriks wrote:
O.K.

An example

The following columns are in the table, and the following data is in it:

Name Type Flower Leaf Light Ground Price

tyme plant red green sun dry 2,75
tyme plant lila green sun dry 15,65

I need to get the average price of all plants that need "sun" as light. But I don't know how I can select ALL prices from ALL plants EXCEPT the ones that DO NOT have "sun" as a light type.

Hope that was clear enough.

Let A3:G50 house the data, with light values in E3:E50 and prices in G3:G50...

Let I1 houses the light condition, e.g., "sun" (without double quotes)...

=SUMIF(E3:E50,I1,G3:G50)/MAX(1,COUNTIF(E3:E50,I1))

would compute the desired average price for plants that require the sun light.

On 2002-09-26 11:10, Jeffrey Hendriks wrote:
O.K.

An example

The following columns are in the table, and the following data is in it:

Name Type Flower Leaf Light Ground Price

tyme plant red green sun dry 2,75
tyme plant lila green sun dry 15,65

I need to get the average price of all plants that need "sun" as light. But I don't know how I can select ALL prices from ALL plants EXCEPT the ones that DO NOT have "sun" as a light type.

Hope that was clear enough.

Let A3:G50 house the data, with light values in E3:E50 and prices in G3:G50...

Let I1 houses the light condition, e.g., "sun" (without double quotes)...

=SUMIF(E3:E50,I1,G3:G50)/MAX(1,COUNTIF(E3:E50,I1))

would compute the desired average price for plants that require the sun light.

Replies
12
Views
702
Replies
11
Views
472
Replies
7
Views
226
Replies
0
Views
207
Replies
15
Views
390

1,217,438
Messages
6,136,618
Members
450,022
Latest member
Joel1122331

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back