Help with Excel Formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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)
 

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!!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top