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+(H 65-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

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+(H 65-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

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+(H 65-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

Try...

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

Hope this helps!

In article ,
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+(H 65-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

Make that...

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

Hope this helps!

In article ,
Domenic wrote:

Try...

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

Hope this helps!

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 ,
Domenic wrote:

Try...

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

Hope this helps!

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+(H 65-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

Actual formula is:

=IF(H65<=700000,D65*0.6,IF(AND(H65=700001,H65<=14 00000),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+(H 65-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

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<=14 00000),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+(H 65-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

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" wrote in message
...
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+(H 65-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

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" wrote in message
...
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+(H 65-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

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" wrote in message
...
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" wrote in message
...
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+(H 65-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 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+(H 65-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

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+(H 65-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

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 ,
Joan 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+(H 65-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

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 ,
Joan 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+(H 65-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