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

 

[SpillerBD]

Why is P9 not always multiplied by 52 (weeks)?

 

[Dave Patton]

=P9*40+Q9*12 gives the same results as =SUM(P9*40+Q9*12)

Can you post an example of your data and describe what you want to calculate.
Please also state your expected results.

There are tools available to post examples on to the forum.

See expected Add-in at the top of the messages.

 

[bionicle]

That's a question I cant answer as the person that created the formula no longer works within our business thus the need to review it.

 

[Fluff]

Probably easier ways, but as a start how about
=T9+S9*IF(T9>0,1,2)+R9*IF(S9>0,2,IF(T9>0,3,4))+Q9*IF(R9>0,8,IF(S9>0,10,IF(T9>0,11,12)))+P9*IF(Q9>0,40,IF(R9>0,48,IF(S9>0,50,IF(T9>0,51,52))))*D9

Although I don't understand the reasoning.
P9*52 drops to P9*40 if there is a monthly value, which i understand
but why do you get P9*51 if you have a yearly value, I would have thought it should be P9*0

 

[bionicle]

hi Fluff

formula works a treat, how would put the iferror statement onto this?

Steve

 

[bionicle]

Hi Fluff,

Me again, jus noticed that the multiplier isn't multiplying the value, an ideas?

 

[bionicle]

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

 

[steve the fish]

Just reading that formula and im intreged as to what its used for. Why do you keep using approx 3/4 of one measure and adding it to a years worth of another? eg:

SUM(P9*40+Q9*12)

 

[bionicle]

the formula calculates time, it should add hours together based on their value and monthly allocation.