Rather hard school assignment. Help please!

Jeffrey Hendriks

New Member
Joined
Sep 25, 2002
Messages
5
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.

Please help.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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.
 

JohnG

Board Regular
Joined
Feb 18, 2002
Messages
165
Could you give a little more detail a sample perhaps?

Sounds as if a sumif might do you.
 

JohnG

Board Regular
Joined
Feb 18, 2002
Messages
165
Could you give a little more detail a sample perhaps?

Sounds as if a sumif might do you.
 

Jeffrey Hendriks

New Member
Joined
Sep 25, 2002
Messages
5

ADVERTISEMENT

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.
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
 

Jeffrey Hendriks

New Member
Joined
Sep 25, 2002
Messages
5

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,143,916
Messages
5,721,525
Members
422,369
Latest member
redinator

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top