need help with this formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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...
 
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
 
Actual formula is:

=IF(H65<=700000,D65*0.6,IF(AND(H65>=700001,H65<=1400000),D65*0.68,D65*0.7))
 
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
 
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
 
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
 
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%.
Click to expand...

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.
 
Joan said:
My workbook has numerous worksheets each representing a new week o
sales.
Each worksheet shows the previous total sales and the current week'
sales to
arrive at the total todate sales.
If total revenue is equal or under $700,000, then the multiplyin
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))-'wk
apr3'!F87-'Wk1 mar26'!F87

H65 represents total sales to date. The result of this formula is i
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
Click to expand...



Isn't the whole point is to get rid of all the "-'wk2 apr3'!F87-'Wk
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,
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
 
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!
 
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Urgently need help with a formula!!! 6
Urgently need help with a formula!!! 2

Back
Top