# 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

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.

#### turbodante

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

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.

As Seen On