IF,Then statement?

B

bhowe

Hi All
I am trying to find a way to write and if/then statement. I have a
column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a
column (B) of gas MPG. I want to average each type of gas. So I was
thinking of adding a column (C) for each type of gas. If A#=C(header),
THEN place B# in C#. I can then take and average the column. I just
don't know how to write it? OR is there a simplier way to write it?
Thanks
Bill
 
K

Ken Wright

Pivot table will do the whole thing for you in about 60 seconds.

Title your Stations with STAT and the mileages with GAS.

Select the entire dataset including the headers, do Data / Pivot table and
Chart report / Next / Next / Finish

Now drag STAT into the ROW fields, and then drag GAS into the DATA area.
Now right click on any one of the values you see and choose Field settings,
and then on the left of the dialog box in the 'Summarise by' section choose
'Average'.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
S

SteveG

Bill,

In C2.

{=AVERAGE(IF($A$1:$A$5=C1,$B$1:$B$5))}

Then drag across under your headers for each type of gas (C1=Citgo
D1=Exxon etc...). This is an array formula so to get the bracket
outside of the formula, commit with Ctrl-Shift-Enter instead of jus
Enter.



Cheers,

Stev
 
B

bhowe

I haven't used this function before. How do I get the brackets
CTRL,SHFT, "Bracket", ENTER? All at once? Sorry, not very Excel savy!
Thanks
Bil
 
S

SteveG

Bill,

Type in the formula without the brackets like you would normally for
any formula(# 1) but when you would normally type enter to commit to
the formula,don't. Hold down the Ctrl key and Shift key and hit the
Enter key simultaneously. That will result in the curly brackets
around the formula (#2).


1.
=AVERAGE(IF($A$1:$A$5=C1,$B$1:$B$5))

2.

{=AVERAGE(IF($A$1:$A$5=C1,$B$1:$B$5))}

Cheers,

Steve
 
Top