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