Is IF the right way to get what I need as the year progresses?

[Dubs]

Hi folks, first time poster, short time member.

Please help where you can.
P6 R6 T6 and V6 are quarterly values and I need them averaged per month as the data becomes available throughout the year.
Cell C51 is the cell for the ongoing average as the data is added to P6, R6, T6 and V6 throughout the year.
What I need to happen:
1. If there is a value in P6 to divide that value by 3 =IF(P6>0,P6/3)
2. If there is a value in R6, add R6 and P6 and divide that value by 6
3. If there is a value in T6, add T6, R6 and P6 and divide that value by 9
4. If there is a value in V6, add V6, T6, R6 and P6 and divide that value by 12

I have only been able to figure out the first formula. I have tried for the others but get no result.

Thanks,

Dubs

 

[kchiba]

try this formula:
=IF(V6<>0,(V6+T6+R6+P6)/12,IF(T6<>0,(T6+R6+P6)/9,IF(R6<>0,(R6+P6)/6,IF(P6<>0,P6/2,0))))

 

[NARAYANK991]

Hi ,

Another option :

=SUM(P6,R6,T6,V6)/SUM(IF(N(OFFSET($P$6,,{0,2,4,6}))>0,1,0))

entered as an array formula , using SHIFT CTRL ENTER.

When all the 4 cells are either blank or contain 0 , this will display an error value ; if this is not acceptable , you can use an IFERROR around this , as follows :

=IFERROR(SUM(P6,R6,T6,V6)/SUM(IF(N(OFFSET($P$6,,{0,2,4,6}))>0,1,0)),0)


Narayan

 

[Dubs]

AHHH, I was starting with something like =IF(P6>0,P6/3,IF(R6>0,(P6+R6)/6))

For my future learning Is there a reason why to use both <> and is there also a reason to start with the largest function and end with the smallest?

Dubs

 

[Dubs]

BTW, thanks for the replies, it seems i was on a similar track which is good but i couldnt get to the final outcome

dubs

 

[kchiba]

Hi Narayan,

I think there is a flaw in the formula

=IFERROR(SUM(P6,R6,T6,V6)/SUM(IF(N(OFFSET($P$6,,{0,2,4,6}))>0,1,0)),0)

s/b

=IFERROR(SUM(P6,R6,T6,V6)/SUM(IF(N(OFFSET($P$6,,{0,2,4,6}))>0,3,0)),0)

kanti

 

[Dubs]

I noticed a small error in this one too:
=IF(V6<>0,(V6+T6+R6+P6)/12,IF(T6<>0,(T6+R6+P6)/9,IF(R6<>0,(R6+P6)/6,IF(P6<>0,P6/2,0))))
should have been:
=IF(V6<>0,(V6+T6+R6+P6)/12,IF(T6<>0,(T6+R6+P6)/9,IF(R6<>0,(R6+P6)/6,IF(P6<>0,P6/3,0))))

but one number in all that is fine by me.

Dubs

 

[NARAYANK991]

Hi Narayan,

I think there is a flaw in the formula

=IFERROR(SUM(P6,R6,T6,V6)/SUM(IF(N(OFFSET($P$6,,{0,2,4,6}))>0,1,0)),0)

s/b

=IFERROR(SUM(P6,R6,T6,V6)/SUM(IF(N(OFFSET($P$6,,{0,2,4,6}))>0,3,0)),0)

kanti

Hi Kanti ,

Thanks for pointing it out ; my mistake.

Narayan

 

[SirJB7]

Hi, Dubs!

Another option, normal formula:
=SI.ERROR(SUMA(P6;R6;T6;V6)/CONTARA(P6;R6;T6;V6)/3;"") -----> in english: =IFERROR(SUM(P6,R6,T6,V6)/COUNTA(P6,R6,T6,V6)/3,"")

I assumed that if no value is present, it means empty cell; if you tried to mean anything else you should replace COUNTA function by a proper COUNTIF.

1. If there is a value in P6 to divide that value by 3 =IF(P6>0,P6/3)
2. If there is a value in R6, add R6 and P6 and divide that value by 6
3. If there is a value in T6, add T6, R6 and P6 and divide that value by 9
4. If there is a value in V6, add V6, T6, R6 and P6 and divide that value by 12

Regards!

 

[Dubs]

Thanks for the replies and the welcome to the forum.

I have another small issue on the same sheet i'd like some help with, this should be easier.

T6 is empty until stats are released.
T19 =T6/3 is ready to self populate when there is data in T6
Currently it is showing 0.00
Therefore there are a lot of 0.00 until my stats are released across the year.

Is there a way to have it look empty until there is a value in T6?


thanks,

Dubs

 

[Dubs]

could this work?
=IF(T6="","", IF(T6<>0,T6/3))

 

[Dubs]

I modified it a bit so if T6 was 0 then it would not return a value of false

=IF(T6="","", IF(T6<>0,T6/3,IF(T6=0,0)))

Does this seem right?

Dubs

 

[kchiba]

You can set the option in Excel to suppress zero values as per attached GIF