need help with this formula

[Joan]

My workbook has numerous worksheets each representing a new week of sales.
Each worksheet shows the previous total sales and the current week's sales to
arrive at the total todate sales.
If total revenue is equal or under $700,000, then the multiplying factor is
$0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
1,400,001 and over the multiplying factor is $0.70.

cell Current week Prior week Total to date
(D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75

The formula is if
(H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87

H65 represents total sales to date. The result of this formula is in cell
F87.
Using this formula however, I need to subtract all the previous weeks'
formula results and so the formula is getting very large as we go on .
Any suggestions?
Joan

[Nigel 2000]

If H65 = F65 + D65 then all you need to subtract is F65 which should be the
culmination of all the previous weeks if i have read your logic correctly

"Joan" wrote:

> My workbook has numerous worksheets each representing a new week of sales.
> Each worksheet shows the previous total sales and the current week's sales to
> arrive at the total todate sales.
> If total revenue is equal or under $700,000, then the multiplying factor is
> $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> 1,400,001 and over the multiplying factor is $0.70.
>
> cell Current week Prior week Total to date
> (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
>
> The formula is if
> (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87
>
> H65 represents total sales to date. The result of this formula is in cell
> F87.
> Using this formula however, I need to subtract all the previous weeks'
> formula results and so the formula is getting very large as we go on .
> Any suggestions?
> Joan

[Joan]

Nope, doesn't work.
Logic is the formula should calculate the 60%, 68% or 70% on the current
week sales, but if the total to date is 700,000 or less then use the 60%; if
700,001 to 1,400,000 then use 68% etc.
In the example I gave earlier, the correct answer would be 1,624.35
Joan

"Nigel 2000" wrote:

> If H65 = F65 + D65 then all you need to subtract is F65 which should be the
> culmination of all the previous weeks if i have read your logic correctly
>
> "Joan" wrote:
>
> > My workbook has numerous worksheets each representing a new week of sales.
> > Each worksheet shows the previous total sales and the current week's sales to
> > arrive at the total todate sales.
> > If total revenue is equal or under $700,000, then the multiplying factor is
> > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > 1,400,001 and over the multiplying factor is $0.70.
> >
> > cell Current week Prior week Total to date
> > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> >
> > The formula is if
> > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87
> >
> > H65 represents total sales to date. The result of this formula is in cell
> > F87.
> > Using this formula however, I need to subtract all the previous weeks'
> > formula results and so the formula is getting very large as we go on .
> > Any suggestions?
> > Joan

[Domenic]

Try...

=LOOKUP(H65,{0,700001,1400000},{0.6,0.68,0.7})*D65

Hope this helps!

In article <[email protected]>,
Joan <[email protected]> wrote:

> My workbook has numerous worksheets each representing a new week of sales.
> Each worksheet shows the previous total sales and the current week's sales to
> arrive at the total todate sales.
> If total revenue is equal or under $700,000, then the multiplying factor is
> $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> 1,400,001 and over the multiplying factor is $0.70.
>
> cell Current week Prior week Total to date
> (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
>
> The formula is if
> (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+
> (H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87
>
> H65 represents total sales to date. The result of this formula is in cell
> F87.
> Using this formula however, I need to subtract all the previous weeks'
> formula results and so the formula is getting very large as we go on .
> Any suggestions?
> Joan

[Domenic]

Make that...

=LOOKUP(H65,{0,700001,1400001},{0.6,0.68,0.7})*D65

Hope this helps!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Try...
>
> =LOOKUP(H65,{0,700001,1400000},{0.6,0.68,0.7})*D65
>
> Hope this helps!

[Joan]

Nope, still doesn't work.
Let me give you the exact numbers:
week 1 sales - 10,228.75; total to-date 10,228.75 - result s/b 6,137.25
week 2 sales - 985,131,25; plus wk 1 = 995,360.00 - result s/b 613,889.25 or
614,707.55 (depending on whether I'm taking current week sales or todate
sales)
week 3 sales - 2,388.75; plus wk 1 & 2 = 997,748.75 - result s/b 1,624.35

10,228.75 is less than 700,000, so multiply by 60%
995,360 total to date is move than 700,000 but less than 1400000 so take
first 700,000 multiplied by 60% and balance 285,360 muliplied by 68%
2,388.75 adds to wk 1 & 2 to equal 997,748.75; the first 700,000 multipled
by 60%, the balance multiplied by 68%.

This is very confusing....thanks so much for your help...


"Domenic" wrote:

> Make that...
>
> =LOOKUP(H65,{0,700001,1400001},{0.6,0.68,0.7})*D65
>
> Hope this helps!
>
> In article <[email protected]>,
> Domenic <[email protected]> wrote:
>
> > Try...
> >
> > =LOOKUP(H65,{0,700001,1400000},{0.6,0.68,0.7})*D65
> >
> > Hope this helps!
>

[Nigel 2000]

In that case after testing condition replace H65 with D65, then there is no
need to subtract previous

Replace H65 with D65 in the True/False sections
"Joan" wrote:

> Nope, doesn't work.
> Logic is the formula should calculate the 60%, 68% or 70% on the current
> week sales, but if the total to date is 700,000 or less then use the 60%; if
> 700,001 to 1,400,000 then use 68% etc.
> In the example I gave earlier, the correct answer would be 1,624.35
> Joan
>
> "Nigel 2000" wrote:
>
> > If H65 = F65 + D65 then all you need to subtract is F65 which should be the
> > culmination of all the previous weeks if i have read your logic correctly
> >
> > "Joan" wrote:
> >
> > > My workbook has numerous worksheets each representing a new week of sales.
> > > Each worksheet shows the previous total sales and the current week's sales to
> > > arrive at the total todate sales.
> > > If total revenue is equal or under $700,000, then the multiplying factor is
> > > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > > 1,400,001 and over the multiplying factor is $0.70.
> > >
> > > cell Current week Prior week Total to date
> > > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> > >
> > > The formula is if
> > > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87
> > >
> > > H65 represents total sales to date. The result of this formula is in cell
> > > F87.
> > > Using this formula however, I need to subtract all the previous weeks'
> > > formula results and so the formula is getting very large as we go on .
> > > Any suggestions?
> > > Joan

[Nigel 2000]

Actual formula is:

=IF(H65<=700000,D65*0.6,IF(AND(H65>=700001,H65<=1400000),D65*0.68,D65*0.7))

"Nigel 2000" wrote:

> In that case after testing condition replace H65 with D65, then there is no
> need to subtract previous
>
> Replace H65 with D65 in the True/False sections
> "Joan" wrote:
>
> > Nope, doesn't work.
> > Logic is the formula should calculate the 60%, 68% or 70% on the current
> > week sales, but if the total to date is 700,000 or less then use the 60%; if
> > 700,001 to 1,400,000 then use 68% etc.
> > In the example I gave earlier, the correct answer would be 1,624.35
> > Joan
> >
> > "Nigel 2000" wrote:
> >
> > > If H65 = F65 + D65 then all you need to subtract is F65 which should be the
> > > culmination of all the previous weeks if i have read your logic correctly
> > >
> > > "Joan" wrote:
> > >
> > > > My workbook has numerous worksheets each representing a new week of sales.
> > > > Each worksheet shows the previous total sales and the current week's sales to
> > > > arrive at the total todate sales.
> > > > If total revenue is equal or under $700,000, then the multiplying factor is
> > > > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > > > 1,400,001 and over the multiplying factor is $0.70.
> > > >
> > > > cell Current week Prior week Total to date
> > > > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> > > >
> > > > The formula is if
> > > > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87
> > > >
> > > > H65 represents total sales to date. The result of this formula is in cell
> > > > F87.
> > > > Using this formula however, I need to subtract all the previous weeks'
> > > > formula results and so the formula is getting very large as we go on .
> > > > Any suggestions?
> > > > Joan

[Joan]

The formula you suggest is multiplying the full current week against
whichever factor fits. What it needs to do, is multiply the first 700000
sales by 60%, between 7000001 and 1,400,000 by 68% and anything over
1,400,000 by 70%. See my original formula.
Joan

"Nigel 2000" wrote:

> Actual formula is:
>
> =IF(H65<=700000,D65*0.6,IF(AND(H65>=700001,H65<=1400000),D65*0.68,D65*0.7))
>
> "Nigel 2000" wrote:
>
> > In that case after testing condition replace H65 with D65, then there is no
> > need to subtract previous
> >
> > Replace H65 with D65 in the True/False sections
> > "Joan" wrote:
> >
> > > Nope, doesn't work.
> > > Logic is the formula should calculate the 60%, 68% or 70% on the current
> > > week sales, but if the total to date is 700,000 or less then use the 60%; if
> > > 700,001 to 1,400,000 then use 68% etc.
> > > In the example I gave earlier, the correct answer would be 1,624.35
> > > Joan
> > >
> > > "Nigel 2000" wrote:
> > >
> > > > If H65 = F65 + D65 then all you need to subtract is F65 which should be the
> > > > culmination of all the previous weeks if i have read your logic correctly
> > > >
> > > > "Joan" wrote:
> > > >
> > > > > My workbook has numerous worksheets each representing a new week of sales.
> > > > > Each worksheet shows the previous total sales and the current week's sales to
> > > > > arrive at the total todate sales.
> > > > > If total revenue is equal or under $700,000, then the multiplying factor is
> > > > > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > > > > 1,400,001 and over the multiplying factor is $0.70.
> > > > >
> > > > > cell Current week Prior week Total to date
> > > > > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> > > > >
> > > > > The formula is if
> > > > > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87
> > > > >
> > > > > H65 represents total sales to date. The result of this formula is in cell
> > > > > F87.
> > > > > Using this formula however, I need to subtract all the previous weeks'
> > > > > formula results and so the formula is getting very large as we go on .
> > > > > Any suggestions?
> > > > > Joan

[Roger Govier]

Hi Joan

Try
=H65*0.6+(MAX(0,H65-700000)*0.08)+(MAX(0,H65-1400000)*0.02)

This multiplies the whole value by 60% and adds a further 8% (to that
60%) for all value that exceeds 700,000 and a further 2% ( to the 60% +
8%) for the value that exceeds 1,400,000

--
Regards

Roger Govier


"Joan" <[email protected]> wrote in message
news:[email protected]...
> My workbook has numerous worksheets each representing a new week of
> sales.
> Each worksheet shows the previous total sales and the current week's
> sales to
> arrive at the total todate sales.
> If total revenue is equal or under $700,000, then the multiplying
> factor is
> $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> 1,400,001 and over the multiplying factor is $0.70.
>
> cell Current week Prior week Total to date
> (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
>
> The formula is if
> (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2
> apr3'!F87-'Wk1 mar26'!F87
>
> H65 represents total sales to date. The result of this formula is in
> cell
> F87.
> Using this formula however, I need to subtract all the previous weeks'
> formula results and so the formula is getting very large as we go on .
> Any suggestions?
> Joan

[Joan]

Nope, that doesn't work either.
The correct answer should be 614,707.55. Your answer is 620,844.80.
Nigel - your answer is 669,889.25. I need a formula that takes 700,000 less
prior weeks multiplied by 60%. Then, additional sales between 700,001 and
1,400,000 multipled by 68%, and then, any sales over 1,400,001 multipled by
70%. What is confusing is that we have current week sales, prior week sales
and total to-date sales. The calculation is based on a split of 60/40 (for
example) of current week sales. So the 60/40 or 68/32 or 70/30 split has to
add up to current week sales, not total to-date, but in order to calculate
the splits, the total-to-date sales determines that.
Very confusing...
Thanks guys for all your help so far...hope we can nail this today.
Joan

"Roger Govier" wrote:

> Hi Joan
>
> Try
> =H65*0.6+(MAX(0,H65-700000)*0.08)+(MAX(0,H65-1400000)*0.02)
>
> This multiplies the whole value by 60% and adds a further 8% (to that
> 60%) for all value that exceeds 700,000 and a further 2% ( to the 60% +
> 8%) for the value that exceeds 1,400,000
>
> --
> Regards
>
> Roger Govier
>
>
> "Joan" <[email protected]> wrote in message
> news:[email protected]...
> > My workbook has numerous worksheets each representing a new week of
> > sales.
> > Each worksheet shows the previous total sales and the current week's
> > sales to
> > arrive at the total todate sales.
> > If total revenue is equal or under $700,000, then the multiplying
> > factor is
> > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > 1,400,001 and over the multiplying factor is $0.70.
> >
> > cell Current week Prior week Total to date
> > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> >
> > The formula is if
> > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2
> > apr3'!F87-'Wk1 mar26'!F87
> >
> > H65 represents total sales to date. The result of this formula is in
> > cell
> > F87.
> > Using this formula however, I need to subtract all the previous weeks'
> > formula results and so the formula is getting very large as we go on .
> > Any suggestions?
> > Joan
>
>
>

[Roger Govier]

Hi Joan

I beg to differ.
In a previous posting you gave real value examples.

Week YTD Result
10228.75 10228.75 6137.25
985131.25 995360.00 614707.55
2388.75 997748.75 1624.35

Your result for the 995360 value, you said was arrived at by multiplying
the first 700,000 by 60% which is 420,000.
You then said the balance of 285,360 should be multiplied by 68% which
is 194,044.80.
The sum of these two comes to 614,044.80 not 614707.55 as you state.
Equally, when you take 700,000 away from 995,360 the difference is
295,360, not 285,360, which when multiplied by 68% gives
200,844.80 which when added to 420,000 gives the result of 620844.80 -
the same as my formula result.

In your latest posting, apart from saying the figures given by others
are wrong, you then say
>I need a formula that takes 700,000 less prior weeks multiplied by 60%.

What does this mean?
Does it mean
(700,000 less prior weeks) multiplied by 60% or
700,000 less (prior weeks multiplied by 60%) ?
What are prior weeks? Prior weeks (how many) or Prior Week's?
And, prior weeks what? Prior weeks sales, prior weeks resulting
calculation of the formula.

What is does look like to me, on reading what I have set out above, is
that you want to do the calculation as per my formula, but then deduct
the cumulative of that calculation up to the previous week.

So with data in columns A to C, with headings in row 1, the formula in
C2 would be
=B2*0.6+(MAX(0,B2-700000)*0.08)+(MAX(0,B2-1400000)*0.02)-sum($C$1:C1)

This copied down would give results of 6137.25, 614707.55, 1624.35 so I
assume that this is what you mean.

--
Regards

Roger Govier


"Joan" <[email protected]> wrote in message
news:[email protected]...
> Nope, that doesn't work either.
> The correct answer should be 614,707.55. Your answer is 620,844.80.
> Nigel - your answer is 669,889.25. I need a formula that takes
> 700,000 less
> prior weeks multiplied by 60%. Then, additional sales between 700,001
> and
> 1,400,000 multipled by 68%, and then, any sales over 1,400,001
> multipled by
> 70%. What is confusing is that we have current week sales, prior week
> sales
> and total to-date sales. The calculation is based on a split of 60/40
> (for
> example) of current week sales. So the 60/40 or 68/32 or 70/30 split
> has to
> add up to current week sales, not total to-date, but in order to
> calculate
> the splits, the total-to-date sales determines that.
> Very confusing...
> Thanks guys for all your help so far...hope we can nail this today.
> Joan
>
> "Roger Govier" wrote:
>
>> Hi Joan
>>
>> Try
>> =H65*0.6+(MAX(0,H65-700000)*0.08)+(MAX(0,H65-1400000)*0.02)
>>
>> This multiplies the whole value by 60% and adds a further 8% (to that
>> 60%) for all value that exceeds 700,000 and a further 2% ( to the 60%
>> +
>> 8%) for the value that exceeds 1,400,000
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Joan" <[email protected]> wrote in message
>> news:[email protected]...
>> > My workbook has numerous worksheets each representing a new week of
>> > sales.
>> > Each worksheet shows the previous total sales and the current
>> > week's
>> > sales to
>> > arrive at the total todate sales.
>> > If total revenue is equal or under $700,000, then the multiplying
>> > factor is
>> > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
>> > 1,400,001 and over the multiplying factor is $0.70.
>> >
>> > cell Current week Prior week Total to
>> > date
>> > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
>> >
>> > The formula is if
>> > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2
>> > apr3'!F87-'Wk1 mar26'!F87
>> >
>> > H65 represents total sales to date. The result of this formula is
>> > in
>> > cell
>> > F87.
>> > Using this formula however, I need to subtract all the previous
>> > weeks'
>> > formula results and so the formula is getting very large as we go
>> > on .
>> > Any suggestions?
>> > Joan
>>
>>
>>

[Morrigan]

My workbook has numerous worksheets each representing a new week of sales.
Each worksheet shows the previous total sales and the current week's sales to
arrive at the total todate sales.
If total revenue is equal or under $700,000, then the multiplying factor is
$0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
1,400,001 and over the multiplying factor is $0.70.

cell Current week Prior week Total to date
(D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75

The formula is if
(H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2 apr3'!F87-'Wk1 mar26'!F87

H65 represents total sales to date. The result of this formula is in cell
F87.
Using this formula however, I need to subtract all the previous weeks'
formula results and so the formula is getting very large as we go on .
Any suggestions?
Joan

Isn't the whole point is to get rid of all the "-'wk2 apr3'!F87-'Wk1 mar26'!F87"? Then wouldn't it just be...

F87=IF(H65<=700000,H65*0.6,IF(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-IF(F65<=700000,F65*0.6,IF(F65<=1400000,700000*0.6+(F65-700000)*0.68,700000*1.28+(F65-1400000)*0.7))

See attachment.

[Joan]

Morrigan,
I think you've got it! We are testing it right now!!!
Thank you so much for the solution.

I also want to thank everyone who sent suggestions for the formula. I
apologize for not being able to explain the problem fully, and therefore,
causing more effort than needed in its resolution.

This is the first time I have ever used Excel Discussion Groups and I am
very impressed. Is there anyway of attaching a spreadsheet in these
discussions. I'm sure if I had done so at the beginning, the solution may
have been much quicker coming.

By the way Morrigan, I was unable to pick up the attachment. I received the
following error message when clicking on the attachment:

Invalid Attachment specified. If you followed a valid link, please notify
the administrator.
I'm not sure what to do next.

Anyways, again many, many thanks.
Joan


"Morrigan" wrote:

>
> Joan Wrote:
> > My workbook has numerous worksheets each representing a new week of
> > sales.
> > Each worksheet shows the previous total sales and the current week's
> > sales to
> > arrive at the total todate sales.
> > If total revenue is equal or under $700,000, then the multiplying
> > factor is
> > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > 1,400,001 and over the multiplying factor is $0.70.
> >
> > cell Current week Prior week Total to date
> > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> >
> > The formula is if
> > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-'wk2
> > apr3'!F87-'Wk1 mar26'!F87
> >
> > H65 represents total sales to date. The result of this formula is in
> > cell
> > F87.
> > Using this formula however, I need to subtract all the previous weeks'
> > formula results and so the formula is getting very large as we go on .
> > Any suggestions?
> > Joan
>
>
>
> Isn't the whole point is to get rid of all the "-'wk2 apr3'!F87-'Wk1
> mar26'!F87"? Then wouldn't it just be...
>
> F87=IF(H65<=700000,H65*0.6,IF(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1.28+(H65-1400000)*0.7))-IF(F65<=700000,F65*0.6,IF(F65<=1400000,700000*0.6+(F65-700000)*0.68,700000*1.28+(F65-1400000)*0.7))
>
> See attachment.
>
>
> +-------------------------------------------------------------------+
> |Filename: Split.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4469 |
> +-------------------------------------------------------------------+
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=523126
>
>

[Domenic]

Here's an alternative to Morrigan's solution...

=SUMPRODUCT(((H65>{0,700000,1400000})*(H65-{0,700000,1400000})-(F65>{0,70
0000,1400000})*(F65-{0,700000,1400000}))*{0.6,0.08,0.02})

Hope this helps!

In article <[email protected]>,
Joan <[email protected]> wrote:

> Morrigan,
> I think you've got it! We are testing it right now!!!
> Thank you so much for the solution.
>
> I also want to thank everyone who sent suggestions for the formula. I
> apologize for not being able to explain the problem fully, and therefore,
> causing more effort than needed in its resolution.
>
> This is the first time I have ever used Excel Discussion Groups and I am
> very impressed. Is there anyway of attaching a spreadsheet in these
> discussions. I'm sure if I had done so at the beginning, the solution may
> have been much quicker coming.
>
> By the way Morrigan, I was unable to pick up the attachment. I received the
> following error message when clicking on the attachment:
>
> Invalid Attachment specified. If you followed a valid link, please notify
> the administrator.
> I'm not sure what to do next.
>
> Anyways, again many, many thanks.
> Joan
>
>
> "Morrigan" wrote:
>
> >
> > Joan Wrote:
> > > My workbook has numerous worksheets each representing a new week of
> > > sales.
> > > Each worksheet shows the previous total sales and the current week's
> > > sales to
> > > arrive at the total todate sales.
> > > If total revenue is equal or under $700,000, then the multiplying
> > > factor is
> > > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > > 1,400,001 and over the multiplying factor is $0.70.
> > >
> > > cell Current week Prior week Total to date
> > > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> > >
> > > The formula is if
> > > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1
> > > .28+(H65-1400000)*0.7))-'wk2
> > > apr3'!F87-'Wk1 mar26'!F87
> > >
> > > H65 represents total sales to date. The result of this formula is in
> > > cell
> > > F87.
> > > Using this formula however, I need to subtract all the previous weeks'
> > > formula results and so the formula is getting very large as we go on .
> > > Any suggestions?
> > > Joan
> >
> >
> >
> > Isn't the whole point is to get rid of all the "-'wk2 apr3'!F87-'Wk1
> > mar26'!F87"? Then wouldn't it just be...
> >
> > F87=IF(H65<=700000,H65*0.6,IF(H65<=1400000,700000*0.6+(H65-700000)*0.68,7000
> > 00*1.28+(H65-1400000)*0.7))-IF(F65<=700000,F65*0.6,IF(F65<=1400000,700000*0.
> > 6+(F65-700000)*0.68,700000*1.28+(F65-1400000)*0.7))
> >
> > See attachment.
> >
> >
> > +-------------------------------------------------------------------+
> > |Filename: Split.zip |
> > |Download: http://www.excelforum.com/attachment.php?postid=4469 |
> > +-------------------------------------------------------------------+
> >
> > --
> > Morrigan
> > ------------------------------------------------------------------------
> > Morrigan's Profile:
> > http://www.excelforum.com/member.php...fo&userid=7094
> > View this thread: http://www.excelforum.com/showthread...hreadid=523126
> >
> >

[Joan]

TWO SOLUTIONS THAT WORK IN THE SAME DAY!!!
I'm in heaven...thanks so very much. You have all been so great. Many,
many thanks for all your help. It has been a pleasure.
Joan
Have a great weekend!

"Domenic" wrote:

> Here's an alternative to Morrigan's solution...
>
> =SUMPRODUCT(((H65>{0,700000,1400000})*(H65-{0,700000,1400000})-(F65>{0,70
> 0000,1400000})*(F65-{0,700000,1400000}))*{0.6,0.08,0.02})
>
> Hope this helps!
>
> In article <[email protected]>,
> Joan <[email protected]> wrote:
>
> > Morrigan,
> > I think you've got it! We are testing it right now!!!
> > Thank you so much for the solution.
> >
> > I also want to thank everyone who sent suggestions for the formula. I
> > apologize for not being able to explain the problem fully, and therefore,
> > causing more effort than needed in its resolution.
> >
> > This is the first time I have ever used Excel Discussion Groups and I am
> > very impressed. Is there anyway of attaching a spreadsheet in these
> > discussions. I'm sure if I had done so at the beginning, the solution may
> > have been much quicker coming.
> >
> > By the way Morrigan, I was unable to pick up the attachment. I received the
> > following error message when clicking on the attachment:
> >
> > Invalid Attachment specified. If you followed a valid link, please notify
> > the administrator.
> > I'm not sure what to do next.
> >
> > Anyways, again many, many thanks.
> > Joan
> >
> >
> > "Morrigan" wrote:
> >
> > >
> > > Joan Wrote:
> > > > My workbook has numerous worksheets each representing a new week of
> > > > sales.
> > > > Each worksheet shows the previous total sales and the current week's
> > > > sales to
> > > > arrive at the total todate sales.
> > > > If total revenue is equal or under $700,000, then the multiplying
> > > > factor is
> > > > $0.60. At 700,001 to 1,400,000 the multiplying factor is $0.68.
> > > > 1,400,001 and over the multiplying factor is $0.70.
> > > >
> > > > cell Current week Prior week Total to date
> > > > (D65) 2,388.75 (F65) 995,360.00 (H65) 997,748.75
> > > >
> > > > The formula is if
> > > > (H65<=700000,H65*0.6,if(H65<=1400000,700000*0.6+(H65-700000)*0.68,700000*1
> > > > .28+(H65-1400000)*0.7))-'wk2
> > > > apr3'!F87-'Wk1 mar26'!F87
> > > >
> > > > H65 represents total sales to date. The result of this formula is in
> > > > cell
> > > > F87.
> > > > Using this formula however, I need to subtract all the previous weeks'
> > > > formula results and so the formula is getting very large as we go on .
> > > > Any suggestions?
> > > > Joan
> > >
> > >
> > >
> > > Isn't the whole point is to get rid of all the "-'wk2 apr3'!F87-'Wk1
> > > mar26'!F87"? Then wouldn't it just be...
> > >
> > > F87=IF(H65<=700000,H65*0.6,IF(H65<=1400000,700000*0.6+(H65-700000)*0.68,7000
> > > 00*1.28+(H65-1400000)*0.7))-IF(F65<=700000,F65*0.6,IF(F65<=1400000,700000*0.
> > > 6+(F65-700000)*0.68,700000*1.28+(F65-1400000)*0.7))
> > >
> > > See attachment.
> > >
> > >
> > > +-------------------------------------------------------------------+
> > > |Filename: Split.zip |
> > > |Download: http://www.excelforum.com/attachment.php?postid=4469 |
> > > +-------------------------------------------------------------------+
> > >
> > > --
> > > Morrigan
> > > ------------------------------------------------------------------------
> > > Morrigan's Profile:
> > > http://www.excelforum.com/member.php...fo&userid=7094
> > > View this thread: http://www.excelforum.com/showthread...hreadid=523126
> > >
> > >
>