1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with Excel Formula

Discussion in 'Business Applications' started by macca925, Mar 30, 2010.

Thread Status:
Not open for further replies.
  1. macca925

    macca925 Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    2
    Hi,

    I am currently using the following Excel formula in cell K2 to calculate and display the average of the lowest two values in the range G2 to J2.

    =SUM(SMALL(G2:J2,1),SMALL(G2:J2,2))/2

    The formula works fine, but I now want it to ignore blank cells, so that if only one value is present in the range (it will be the value already in G2 from a previous season) it will display that value in K2 in the absence of data in H2,I2 and J2. However, I still want the averaging function of the current formula to happen once cells H2 to J2 start having data added.

    I would be grateful for any help or hints as to how to proceed.
    (y):D
     
  2. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Welcome to the forum Macca.

    Does this do what you need?...

    =IF(AND(ISBLANK(H2),ISBLANK(I2),ISBLANK(J2)),G2,SUM(SMALL(G2:J2,1),SMALL(G2:J2,2))/2)
     
  3. macca925

    macca925 Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    2
    Hi turbodante.

    The formula you posted worked a treat, and I actually managed to learn something from it and added functionality to it to get another result I was looking for, but couldn't figure out before!

    Many thanks!!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/913669

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice