Dynamic IF calculations

[Charles_]

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 ; min-height: 14.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px} table.t1 {border-collapse: collapse} td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #aaaaaa #aaaaaa #aaaaaa #aaaaaa ; padding: 1.0px 5.0px 1.0px 5.0px} </style>Hi all,


I've been struggling on how to best convey my question by using the table below. Can you help me work out a formula for column B below, please?


Thank!

	A	B	c
1	Completed	Average	Projected - dynamically calculated
2	20	=A2	=B2*4
3	25	=SUM(A3+A2)/2	=B3*4
4		if A4 is blank, use latest available average in column B, in this case B3 If there is a value in A4: =SUM(A4+A3+A2)/3	=B4*4
5		if A5 is blank, use latest available average in column B, in this case B3 If there is a value in A5: =SUM(A5+A4+A3+A2)/4	= B5*4

<tbody>
</tbody>

 

[Indystick]

=IF(ISBLANK(A4),B3,AVERAGE($A$2:A4))<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 ; min-height: 14.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px} table.t1 {border-collapse: collapse} td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #aaaaaa #aaaaaa #aaaaaa #aaaaaa ; padding: 1.0px 5.0px 1.0px 5.0px} </style>

 

[Fluff]

In B2 & fill down
=AGGREGATE(1,6,A$2:A2)

 

[Charles_]

In B2 & fill down
=AGGREGATE(1,6,A$2:A2)

Thanks Fluff, almost works like I needed!

The only issue I have is if I don't have a value on B2, then the formula breaks. I realise in the example above I had a value but I won't always have one, is this something you can help me with?

 

[Fluff]

How about
=IFERROR(AGGREGATE(1,6,A$2:A2),"")

 

[Charles_]

Thanks! that worked great!

 

[Fluff]

You're welcome