excel 2007 help

Posted on 2011-05-06
Last Modified: 2012-05-11
Is there a way to average a cell if the cell above it has a crertain value??

So if row b1 through b5 has percentages in it I want to add the cell and then give an average if in the row above the data has an S in it.

A1 = S  A2 = B A3 = S A4 = S A5 = B
B1 = 2 B2 = 2 B3 = 7 B4 =9 B5 = 6

Add B1+B3+B5 then average them.  Total would change as the values in A change.
Question by:HDM
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Something is not clear here. Why did you select B1, B3 and B5? They dont have an S in their row, nor in the row above for all.
    LVL 32

    Accepted Solution

    I agree, but does the attached do what you are after?  It simply creates another (hidden) column that captures your cells based on your criteria of one column having an S.  If not, I'm afraid I misunderstood what you're looking for :)
    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    If PowerEdgeTech has understood you correctly you do not need a helper column. You can simply use the formulas



    Author Closing Comment

    Thank you!!

    Perfect!!  :-)

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now