View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Formula in a cell to calc FUTA tax

Improvement #2....

On Nov 16, 9:13 am, I wrote:
Alternatively, the formula in H2 could be:
=if(A2="", "", min(round(A2*0.8%, 2), 56))
and the formula starting in H3 (and copy down) could be:
=if(A3="", "",
min(round(A3*0.8%, 2), max(0, 56-round(sum($A$2:A2)*0.8%, 2))))


Silly me! The second formula can simply be:
=IF(A3="", "", min(round(A3*0.8%,2), max(0, 56-sum($H$2:H2))))


(Silly me)^2! You could simply put the following formula into H2 and
copy down:

=if(A3="", "", min(round(A3*0.8%,2), max(0, 56-sum($H$1:H1))))

This assumes that H1 is blank or a column heading, and it relies on
the fact that SUM() does not include cells with text.

By the way, my first formula [1] is arguably more reliable because it
is based on cumulative wages. The formulas based on cumulative FUTA
suffer from the defect that if periodic FUTA rounds to zero,
cumulative FUTA might not sum to 56. But that is unlikely since that
means that gross wages are less than $0.62(!). I suspect you do not
even need to report wages in that case ;-).


-----

[1] First formula:

=if(A2="", "",
min(round(A2*0.8%, 2), max(0, 56-round((sum($A$2:A2)-A2)*0.8%,
2))))