Return value based on contents of another cell

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

I have a spreadsheet with about 20 columns. In column "L" each cell has
a value of "Rich-Burn" or "Lean-Burn". Then in column "N" I have
number values corresponding to "Rich-Burn" or "Lean-Burn".

I would like to average all of the individual values in column "N" to
get a total number for "Rich-Burn". Similarly, "Lean-Burn" also needs
to have one value that represents the average.

New values will be constantly added to this spreadsheet, so it would be
a great help if there is a formula to automatically calculate this.
Can anyone help??


Thanks.
 
This array entered formula will do it

=AVERAGE(IF(L2:L200="Rich-Burn",N2:N200))

entered with ctrl + shift & enter

for better usability replace the "Rich-Burn" with a cell where you can type
in the different criteria
 
I have a spreadsheet with about 20 columns. In column "L" each cell has
a value of "Rich-Burn" or "Lean-Burn". Then in column "N" I have
number values corresponding to "Rich-Burn" or "Lean-Burn".

I would like to average all of the individual values in column "N" to
get a total number for "Rich-Burn". Similarly, "Lean-Burn" also needs
to have one value that represents the average.

New values will be constantly added to this spreadsheet, so it would be
a great help if there is a formula to automatically calculate this.
Can anyone help??


Thanks.
Click to expand...

Something like:

=SUMIF(L1:L1000,"Rich-Burn",N1:N1000)/COUNTIF(L1:L1000,"Rich-Burn")


--ron
 
To make this slightly more complicated...

Is there a way to add an extra "IF" statement into the formula.


=AVERAGE(IF(L2:L200="Rich-Burn",N2:N200)) and

IF(M2:M200=560, N2:N200)


where column "M" contains varying horsepower ratings.


I would like to average the values that correspond to a certain Engine
Type (Rich/Lean) and also to a certain horsepower (560, 600, 260,
etc).


Thanks.
 
Back
Top