# Help with Excel Formula

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

Not open for further replies.

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.

2. ### 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)

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