Sum based on part weeks

[fatreg]

Hi,

I'm struggling awfully with this but am unsure why...

I have

A1 - 3.4 - meaning 3. weeks
A2 - £150 - Daily rate
A3 - Week 1
A4 - Week 2
A5 - Week 3
A6 - Week 4
A7 - Week 5

the only variable is the length, this could be anything from 0.2 to 5.0, .2 denoting 1 day in the week, so .4, .6 .8 respectively.

I need a formula that will work out the weekly rate, so I should expect to see

A3 - £750
A4 - £750
A5 - £750
A6 - £300

I've tried using nested Ifs, if and and if or, but whatever I do it will work out the whole weeks ok but not the part weeks

Please help, it's doing my head in, I know it's a simpleish formula but I just can't fathom it.

thanks in advance

 

[VBA Geek]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A4</th><td style="text-align:left">=MEDIAN(<font color="Blue">(<font color="Red">$A$2*5*$A$1</font>)-SUM(<font color="Red">A$3:A3</font>),0,A$2*5</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013
	A	B
1	3.4
2	150
3
4	750	Week 1
5	750	Week 2
6	750	Week 3
7	300	Week 4
8	0	Week 5
9	0	Week 6
Sheet3

 

[texasalynn]

3.4	meaning 3. weeks
£150.00	Daily rate
Week 1	750
Week 2	750
Week 3	750
Week 4	300
Week 5

<tbody>
</tbody>

formula used:=IF(LEFT($A$1,1)+0>=RIGHT(A3,1)+0,A$2*5,IF(AND(RIGHT($A$1,1)>0,LEFT($A$1,1)+0=RIGHT(A3,1)+0-1),(RIGHT($A$1,1)+0)/2*A$2,""))

 

[fatreg]

thanks both, used the median and that looks tidier

 

[fatreg]

right, stuck again now

I now need to top up the weekly amount to £1400,

So,

3.4
150
1500

Week 1 - 750
Week 2 - 750
Week 3 - 750
Week 4 - 300
Week 5 - 0

then

Week 1 - 650
Week 2 - 650
Week 3 - 650
Week 4 - 260
Week 5 - 0

I tried to tweak the median to do it but that doesn't appear to be working..

Any ideas?

 

[VBA Geek]

i dont get the second problem, where does 650 come from?
the sum of 650 650 650 260 gives me 2,210 which is not 1400

maybe you meant?

650	Week 1
650	Week 2
100	Week 3
0	Week 4
0	Week 5
0	Week 6

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>