excel formula help!!!

[kitten]

hi guys,
i'm in a bit of trouble! i need to do up an excel sheet, and in which i need to make some charts, however, i need to apply some formulae to the data to be put into these charts! if anyone could help i'd really appreciate it,

ok: (it's abt video rentals)
i have some columns, ABCD, in a = income, b = numrentalstotal

i need to do some sort of IF statement,
so if the column A = 28, get the B value... and add all these for all those with value of 28 and get the average,

i need to get the average of the B columns for all the A = 28....
then i need to do this for each value in column A, into a table so i can make a chart (somehow!)

i'm not sure how to do this?
any help would be much appreciated!!!

Thanks!

Kitten ^_-

 

[murad5]

It sounds like you should use a pivot table. Presuming that you have the words "Income" and "numrantalstotal" in cells A1 and B1, you can run the Pivot Table Wizard. Choose "Excel List or Database" as your data source in the first screen, then select the range with your data in it in the second. In the third screen, select "Layout". A new screen will appear with a grid in the middle and a list of the fields in your range on the right. Put "Income" in the "Row" section of the grid, and "numrentalstotal" in the "Data" section. Double click on "numrentalstotal" and you will get a list of ways to summarise the data. Choose "Average" and then close this list, the screen with the grid, and then click "Finish" on the wizard. This should then create a pivot table with the different values you have in column A and the average values for each of their corresponding values in column B. You can then use the "Chart Wizard" button to create a pivot chart.

Hope this helps, give me a shout if that's not clear...