Simplifying a formula that calculates time.

[bionicle]

Hi all,

Need some help please.

I'm working in a spread sheet that calculates an overall time based on weekly, monthly, quarterly, Bi-annual and yearly time allocation as below (Columns P-T Row9).

[TABLE="width: 288"]
<tbody>[TR]
[TD="width: 79, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]P
[/TD]
[TD="width: 79, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]Q
[/TD]
[TD="width: 76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]R
[/TD]
[TD="width: 76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]S
[/TD]
[TD="width: 74, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]T
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 288"]
<tbody>[TR]
[TD="width: 79, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: center"]Weekly
[/TD]
[TD="width: 79, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: center"]Monthly
[/TD]
[TD="width: 76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: center"]Quarterly
[/TD]
[TD="width: 76, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: center"]Bi-annual
[/TD]
[TD="width: 74, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: center"]Yearly
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]0.25
[/TD]
[TD="align: center"]0.33
[/TD]
[TD="align: center"]0.50
[/TD]
[TD="align: center"]0.50
[/TD]
[TD="align: center"]2.00
[/TD]
[/TR]
</tbody>[/TABLE]

The above accumulated time equals 16.17 hours total for the year however the formula I use is very long and slows everything down, I also multiplies the above by a value in D9 (1 gives the above 2 would double it and so on).

I need help in making the formula smaller and easier to manage, I have used a sum product in the past which calculates well however doesn't give he same results.

the formula being used is:

=IFERROR((IF(AND(P9>0,Q9=0,R9=0,S9=0,T9=0),P9*52,IF(AND(P9>0,Q9>0,R9=0,S9=0,T9=0),SUM(P9*40+Q9*12),IF(AND(P9>0,Q9>0,R9>0,S9=0,T9=0),SUM(P9*40+Q9*8+R9*4),IF(AND(P9>0,Q9>0,R9>0,S9>0,T9=0),SUM(P9*40+Q9*8+R9*2+S9*2),IF(AND(P9>0,Q9>0,R9>0,S9>0,T9>0),SUM(P9*40+Q9*8+R9*2+S9+T9),IF(AND(P9>0,Q9>0,R9=0,S9>0,T9=0),SUM(P9*40+Q9*10+S9*2),IF(AND(P9=0,Q9=0,R9=0,S9=0,T9>0),T9,IF(AND(P9=0,Q9>0,R9>0,S9>0,T9>0),SUM(Q9*8+R9*2+S9+T9),IF(AND(P9>0,Q9=0,R9>0,S9>0,T9>0),SUM(P9*48+R9*2+S9+T9),IF(AND(P9=0,Q9>0,R9=0,S9=0,T9=0),SUM(Q9*12),IF(AND(P9=0,Q9=0,R9>0,S9=0,T9=0),SUM(R9*4),IF(AND(P9>0,Q9=0,R9>0,S9=0,T9=0),SUM(P9*48+R9*4),IF(AND(P9=0,Q9=0,R9=0,S9>0,T9=0),SUM(S9*2),IF(AND(P9=0,Q9>0,R9>0,S9=0,T9=0),SUM(Q9*8+R9*4),IF(AND(P9>0,Q9>0,R9=0,S9>0,T9>0),SUM(P9*40+Q9*10+S9+T9),IF(AND(P9>0,Q9>0,R9>0,S9=0,T9>0),SUM(P9*40+Q9*8+R9*3+T9),IF(AND(P9=0,Q9=0,R9>0,S9>0,T9>0),SUM(R9*2+S9+T9),IF(AND(P9=0,Q9>0,R9=0,S9>0,T9>0),SUM(P9*40+Q9*10+S9+T9),IF(AND(P9>0,Q9=0,R9=0,S9>0,T9>0),SUM(P9*50+S9+T9),IF(AND(P9>0,Q9=0,R9>0,S9=0,T9>0),SUM(P9*48+R9*3+T9),IF(AND(P9=0,Q9>0,R9>0,S9>0,T9=0),SUM(Q9*8+R9*2+S9*2),IF(AND(P9>0,Q9>0,R9=0,S9=0,T9>0),SUM(P9*40+Q9*11+T9),IF(AND(P9=0,Q9=0,R9=0,S9>0,T9>0),SUM(S9+T9),IF(AND(P9=0,Q9=0,R9>0,S9=0,T9>0),SUM(R9*3+T9),IF(AND(P9=0,Q9>0,R9=0,S9=0,T9>0),SUM(Q9*11+T9),IF(AND(P9=0,Q9=0,R9>0,S9>0,T9=0),SUM(R9*2+S9*2),IF(AND(P9>0,Q9=0,R9=0,S9=0,T9>0),SUM(P9*51+T9),IF(AND(P9=0,Q9>0,R9=0,S9>0,T9=0),SUM(Q9*10+S9*2),IF(AND(P9>0,Q9=0,R9=0,S9>0,T9=0),SUM(P9*50+S9*2),IF(AND(P9=0,Q9>0,R9>0,S9=0,T9>0),SUM(Q9*8+R9*3+T9),""))))))))))))))))))))))))))))))*D9),0)

As you can see its a beast, is there a way o make this smaller with the same results?

There area few virtual beers given and a huge thank you for an help.

thanks

Steve

 

[steve the fish]

Its still unclear to me why you would multiply a weekly allocation by 40 or a monthly allocation by 8 to get a yearly allocation.

 

[Fluff]

Problem solved...create of virtual beer heading your way..

Glad we could help & thanks for the feedback.