Calculating only non-empty cells...
 

Guru's

I have a question which may be a brain buster! "Can I have Excel devide a
resulting value by only the number of cells containing data? Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum() function in cell
"U5", however, the total value is now looked at using a "VLookUp" function on
another sheet to see what the Tax liability is and places that value in cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then, that is
great, all I need to is devide the total Tax liability by those 12 months to
get my monthly Tax liability value in cell "H6" - "S6"! But, what if the
values run from say; "M5" - "S5", can I have the Tax liability the devided
(calculated) by only those cells in "M6" - "S6"? I need the formula to look
at the cells in reverse from "S5" back to "H5", see if any are blank or empty
cells, trip those off and devide the total value in "V5" or "W5" by only
those cells placing the results in whatever cells need to show a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But, really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

Rather than dividing by 12, just divide by COUNT(H5:S5). Count ignores blank
cells, so you'll get the total number of cells that have data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have Excel devide a
resulting value by only the number of cells containing data? Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum() function in cell
"U5", however, the total value is now looked at using a "VLookUp" function on
another sheet to see what the Tax liability is and places that value in cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then, that is
great, all I need to is devide the total Tax liability by those 12 months to
get my monthly Tax liability value in cell "H6" - "S6"! But, what if the
values run from say; "M5" - "S5", can I have the Tax liability the devided
(calculated) by only those cells in "M6" - "S6"? I need the formula to look
at the cells in reverse from "S5" back to "H5", see if any are blank or empty
cells, trip those off and devide the total value in "V5" or "W5" by only
those cells placing the results in whatever cells need to show a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But, really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

Thanks Fred:

Now, as I attempt to populate the cells below which is row H6:S6, how would
I get that to fill in only a value in row H6:S6 or whatever based on the
populated cells above in H5:S5? This is where I get totally lost! Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5). Count ignores blank
cells, so you'll get the total number of cells that have data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have Excel devide a
resulting value by only the number of cells containing data? Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum() function in cell
"U5", however, the total value is now looked at using a "VLookUp" function on
another sheet to see what the Tax liability is and places that value in cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then, that is
great, all I need to is devide the total Tax liability by those 12 months to
get my monthly Tax liability value in cell "H6" - "S6"! But, what if the
values run from say; "M5" - "S5", can I have the Tax liability the devided
(calculated) by only those cells in "M6" - "S6"? I need the formula to look
at the cells in reverse from "S5" back to "H5", see if any are blank or empty
cells, trip those off and devide the total value in "V5" or "W5" by only
those cells placing the results in whatever cells need to show a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But, really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

It's the other way around, Jay. You give us the example, and we'll show you how
to do it. I can't tell from your question what you are looking for. Tell us
what's in H5, and what you want to see in H6. It could be as simple as
=if(h5="","",yourformulahere), but I can't tell until I know what results you
want.

--
Regards,
Fred


"Jay" wrote in message
...
Thanks Fred:

Now, as I attempt to populate the cells below which is row H6:S6, how would
I get that to fill in only a value in row H6:S6 or whatever based on the
populated cells above in H5:S5? This is where I get totally lost! Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5). Count ignores blank
cells, so you'll get the total number of cells that have data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have Excel devide a
resulting value by only the number of cells containing data? Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum() function in cell
"U5", however, the total value is now looked at using a "VLookUp" function
on
another sheet to see what the Tax liability is and places that value in
cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then, that is
great, all I need to is devide the total Tax liability by those 12 months
to
get my monthly Tax liability value in cell "H6" - "S6"! But, what if the
values run from say; "M5" - "S5", can I have the Tax liability the devided
(calculated) by only those cells in "M6" - "S6"? I need the formula to look
at the cells in reverse from "S5" back to "H5", see if any are blank or
empty
cells, trip those off and devide the total value in "V5" or "W5" by only
those cells placing the results in whatever cells need to show a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But, really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

Okay,..Fred, simple request, here goes...

At times row/cells H5:S5 may contain a value like 1380.00 in everyone of the
cells depending on if the employee became employeed the full year. However,
if he/she started half way into the year, say cell M5:S5, then, only about
half of those cells would have a value of 1380.00. The =Sum() function
generates a Tax value (in cell W5) based on the total! However, that total
number of cells containing a value drops off by half for HALF a year. So I
need to devide the total tax value by whatever number of cells have values,
either ALL of the cells from S6:H6 going backwards/right to left (or half
that number), instead of left to right in row/cells H5:S5!
================
Income Example #1
H5 I5 J5 K5 L5 M5 N5 O5 P5
Q5 R5 S5
1380 1380 1380 1380 1380 1380 1380 1380 1380 1380
1380 1380
Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6 P6
Q6 R6 S6
25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00
25.00 25.00
================
Half Year Income Example #2
H5 I5 J5 K5 L5 M5 N5 O5 P5
Q5 R5 S5
1380 1380 1380
1380 1380 1380 1380
Half Year Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6 P6
Q6 R6 S6
25.00 25.00 25.00
25.00 25.00 25.00 25.00
================

I hope this example explains what I'm trying to accomplish!!!

I need to show an Income tax value in the row/cells below in H6:S6, which
would be tied to the income values in the cells H5:S5 or only half.

Than, again Fred....



"Fred Smith" wrote:

It's the other way around, Jay. You give us the example, and we'll show you how
to do it. I can't tell from your question what you are looking for. Tell us
what's in H5, and what you want to see in H6. It could be as simple as
=if(h5="","",yourformulahere), but I can't tell until I know what results you
want.

--
Regards,
Fred


"Jay" wrote in message
...
Thanks Fred:

Now, as I attempt to populate the cells below which is row H6:S6, how would
I get that to fill in only a value in row H6:S6 or whatever based on the
populated cells above in H5:S5? This is where I get totally lost! Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5). Count ignores blank
cells, so you'll get the total number of cells that have data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have Excel devide a
resulting value by only the number of cells containing data? Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum() function in cell
"U5", however, the total value is now looked at using a "VLookUp" function
on
another sheet to see what the Tax liability is and places that value in
cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then, that is
great, all I need to is devide the total Tax liability by those 12 months
to
get my monthly Tax liability value in cell "H6" - "S6"! But, what if the
values run from say; "M5" - "S5", can I have the Tax liability the devided
(calculated) by only those cells in "M6" - "S6"? I need the formula to look
at the cells in reverse from "S5" back to "H5", see if any are blank or
empty
cells, trip those off and devide the total value in "V5" or "W5" by only
those cells placing the results in whatever cells need to show a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But, really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

sSorry, I missed/forgot the most important part. The TAX LIABILITY value in
W5 get devided into either ALL the cells in H6:S6 or only part/half the year
as in M6:S6, if only employed half a year.

Thanks once more...



"Jay" wrote:

Okay,..Fred, simple request, here goes...

At times row/cells H5:S5 may contain a value like 1380.00 in everyone of the
cells depending on if the employee became employeed the full year. However,
if he/she started half way into the year, say cell M5:S5, then, only about
half of those cells would have a value of 1380.00. The =Sum() function
generates a Tax value (in cell W5) based on the total! However, that total
number of cells containing a value drops off by half for HALF a year. So I
need to devide the total tax value by whatever number of cells have values,
either ALL of the cells from S6:H6 going backwards/right to left (or half
that number), instead of left to right in row/cells H5:S5!
================
Income Example #1
H5 I5 J5 K5 L5 M5 N5 O5 P5
Q5 R5 S5
1380 1380 1380 1380 1380 1380 1380 1380 1380 1380
1380 1380
Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6 P6
Q6 R6 S6
25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00
25.00 25.00
================
Half Year Income Example #2
H5 I5 J5 K5 L5 M5 N5 O5 P5
Q5 R5 S5
1380 1380 1380
1380 1380 1380 1380
Half Year Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6 P6
Q6 R6 S6
25.00 25.00 25.00
25.00 25.00 25.00 25.00
================

I hope this example explains what I'm trying to accomplish!!!

I need to show an Income tax value in the row/cells below in H6:S6, which
would be tied to the income values in the cells H5:S5 or only half.

Than, again Fred....



"Fred Smith" wrote:

It's the other way around, Jay. You give us the example, and we'll show you how
to do it. I can't tell from your question what you are looking for. Tell us
what's in H5, and what you want to see in H6. It could be as simple as
=if(h5="","",yourformulahere), but I can't tell until I know what results you
want.

--
Regards,
Fred


"Jay" wrote in message
...
Thanks Fred:

Now, as I attempt to populate the cells below which is row H6:S6, how would
I get that to fill in only a value in row H6:S6 or whatever based on the
populated cells above in H5:S5? This is where I get totally lost! Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5). Count ignores blank
cells, so you'll get the total number of cells that have data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have Excel devide a
resulting value by only the number of cells containing data? Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum() function in cell
"U5", however, the total value is now looked at using a "VLookUp" function
on
another sheet to see what the Tax liability is and places that value in
cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then, that is
great, all I need to is devide the total Tax liability by those 12 months
to
get my monthly Tax liability value in cell "H6" - "S6"! But, what if the
values run from say; "M5" - "S5", can I have the Tax liability the devided
(calculated) by only those cells in "M6" - "S6"? I need the formula to look
at the cells in reverse from "S5" back to "H5", see if any are blank or
empty
cells, trip those off and devide the total value in "V5" or "W5" by only
those cells placing the results in whatever cells need to show a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But, really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

Hi Jay

either ALL of the cells from S6:H6 going backwards/right to left (or
half
that number), instead of left to right in row/cells H5:S5!

The direction makes no difference. Take a small example
H I J K
1380 1380 =SUM(H1:K1) = 2760
25 25 =SUM(H2:K2) = 50
1380 1380 =SUM(H3:K3) = 2760
25 25 =SUM(H4:K4) = 50

Fred gave you the answer as to how many values there are
=COUNT(A1:D1) which will return the result of 2 in both cases as above.

It is not clear from your question, what you want to do with your
result.

--
Regards

Roger Govier


"Jay" wrote in message
...
sSorry, I missed/forgot the most important part. The TAX LIABILITY
value in
W5 get devided into either ALL the cells in H6:S6 or only part/half
the year
as in M6:S6, if only employed half a year.

Thanks once more...



"Jay" wrote:

Okay,..Fred, simple request, here goes...

At times row/cells H5:S5 may contain a value like 1380.00 in everyone
of the
cells depending on if the employee became employeed the full year.
However,
if he/she started half way into the year, say cell M5:S5, then, only
about
half of those cells would have a value of 1380.00. The =Sum()
function
generates a Tax value (in cell W5) based on the total! However, that
total
number of cells containing a value drops off by half for HALF a year.
So I
need to devide the total tax value by whatever number of cells have
values,
either ALL of the cells from S6:H6 going backwards/right to left (or
half
that number), instead of left to right in row/cells H5:S5!
================
Income Example #1
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380 1380 1380 1380 1380 1380 1380 1380 1380
1380 1380
Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00
25.00
25.00 25.00
================
Half Year Income Example #2
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380
1380
1380 1380 1380 1380
Half Year Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00
25.00
25.00 25.00 25.00 25.00
================

I hope this example explains what I'm trying to accomplish!!!

I need to show an Income tax value in the row/cells below in H6:S6,
which
would be tied to the income values in the cells H5:S5 or only half.

Than, again Fred....



"Fred Smith" wrote:

It's the other way around, Jay. You give us the example, and we'll
show you how
to do it. I can't tell from your question what you are looking for.
Tell us
what's in H5, and what you want to see in H6. It could be as simple
as
=if(h5="","",yourformulahere), but I can't tell until I know what
results you
want.

--
Regards,
Fred


"Jay" wrote in message
...
Thanks Fred:

Now, as I attempt to populate the cells below which is row H6:S6,
how would
I get that to fill in only a value in row H6:S6 or whatever based
on the
populated cells above in H5:S5? This is where I get totally lost!
Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5). Count
ignores blank
cells, so you'll get the total number of cells that have data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have
Excel devide a
resulting value by only the number of cells containing data?
Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum()
function in cell
"U5", however, the total value is now looked at using a
"VLookUp" function
on
another sheet to see what the Tax liability is and places that
value in
cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then,
that is
great, all I need to is devide the total Tax liability by
those 12 months
to
get my monthly Tax liability value in cell "H6" - "S6"! But,
what if the
values run from say; "M5" - "S5", can I have the Tax liability
the devided
(calculated) by only those cells in "M6" - "S6"? I need the
formula to look
at the cells in reverse from "S5" back to "H5", see if any are
blank or
empty
cells, trip those off and devide the total value in "V5" or
"W5" by only
those cells placing the results in whatever cells need to show
a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But,
really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

Okay Roger, here goes...

If I take the =Sum() total of H5:S5 (12X1800), I'll get a value of let's say
21600, the returned value is verified against a lookup tax table using a
VLookUp function which may say that the TAX liability is 1077.70 in cell W5.
This returned value would be correct and based on a Income TAX Table. If I
then took that 1077.70 and devided it amongst the 12 cells ranging from H5 -
S5, I would have a value of 89.81 for each of the 12 month/cell values
between H5 - S5, placing that value in the cell ranges of H6 - S6, below the
H5 - S5 income values since the income tax H6:S6 values will be deducted from
the income values of H5:S5.

Now, what if the person was employed mid year, then, the income range would
only be populted from say M5 - S5, leaving the other cells L5 - H5 (going
right to left) blank or empty! If I had the formula deviding the value in W5
over the 12 cell ranging H6:S6, then, I would have a Income Tax value showing
up in the row below i.e. H6:S6 for which there is NO corresponding income in
the (Blank) cells on the row/cells above H5:S5.

What I need is a function that will take the value in cell W5, then, look at
the cells in the range H5:S5 and if all ALL have a value devide the result in
cell W5 by the 12 cell values, =(W$5/12) in the H6 - S6 cell range. If only
the cell M5 - S5 have values, then, do a check/count of the cells above in H5
- S5 and only devide the value of cell W5 by the number/qty of cells
containing a value which in this case would be M6 - S6 =(W5/6), since H5 - L5
are blank, 0 or have NO value. Basically, I think, it should be a sort of
IfThenElse. If the cell(s) income above have value, then, count how many do,
if 12 (all year) have values, devide by 12, if only 6 have a values (last
half of year), devide by 6 in the H6 - S6 range but only populate M6 - S6!
Placing those values in their respective corresponding cells below
representing income tax on the values in the cells above/income.

I hope this helps explain what I'm looking for. I Do NOT want the user
calculating any Tax Liability value, I want the function to do it seemlessly
for them! If the Income cell above in H5 is empty, 0 or blank, leave the
cell H6 Income Tax like wise empty, 0 or blank. Only place a value in H6 only
if there is a value in H5...

If there are other questions, please post back! I pretty sure any of you may
be more confused now than before! I hope NOT! However, if you need more
clearity, post back?

Oh, a correction, the employee may have either only worked the first half of
the year or the last half of the year, which ever it was, I'll need to do a
check againts empty cells in H5 - S5 to decide which cells in H6 - S6 to
place the returned values in!

Thanks for taking the time to look at this crazy one.....




"Roger Govier" wrote:

Hi Jay

either ALL of the cells from S6:H6 going backwards/right to left (or
half
that number), instead of left to right in row/cells H5:S5!

The direction makes no difference. Take a small example
H I J K
1380 1380 =SUM(H1:K1) = 2760
25 25 =SUM(H2:K2) = 50
1380 1380 =SUM(H3:K3) = 2760
25 25 =SUM(H4:K4) = 50

Fred gave you the answer as to how many values there are
=COUNT(A1:D1) which will return the result of 2 in both cases as above.

It is not clear from your question, what you want to do with your
result.

--
Regards

Roger Govier


"Jay" wrote in message
...
sSorry, I missed/forgot the most important part. The TAX LIABILITY
value in
W5 get devided into either ALL the cells in H6:S6 or only part/half
the year
as in M6:S6, if only employed half a year.

Thanks once more...



"Jay" wrote:

Okay,..Fred, simple request, here goes...

At times row/cells H5:S5 may contain a value like 1380.00 in everyone
of the
cells depending on if the employee became employeed the full year.
However,
if he/she started half way into the year, say cell M5:S5, then, only
about
half of those cells would have a value of 1380.00. The =Sum()
function
generates a Tax value (in cell W5) based on the total! However, that
total
number of cells containing a value drops off by half for HALF a year.
So I
need to devide the total tax value by whatever number of cells have
values,
either ALL of the cells from S6:H6 going backwards/right to left (or
half
that number), instead of left to right in row/cells H5:S5!
================
Income Example #1
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380 1380 1380 1380 1380 1380 1380 1380 1380
1380 1380
Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00
25.00
25.00 25.00
================
Half Year Income Example #2
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380
1380
1380 1380 1380 1380
Half Year Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00
25.00
25.00 25.00 25.00 25.00
================

I hope this example explains what I'm trying to accomplish!!!

I need to show an Income tax value in the row/cells below in H6:S6,
which
would be tied to the income values in the cells H5:S5 or only half.

Than, again Fred....



"Fred Smith" wrote:

It's the other way around, Jay. You give us the example, and we'll
show you how
to do it. I can't tell from your question what you are looking for.
Tell us
what's in H5, and what you want to see in H6. It could be as simple
as
=if(h5="","",yourformulahere), but I can't tell until I know what
results you
want.

--
Regards,
Fred


"Jay" wrote in message
...
Thanks Fred:

Now, as I attempt to populate the cells below which is row H6:S6,
how would
I get that to fill in only a value in row H6:S6 or whatever based
on the
populated cells above in H5:S5? This is where I get totally lost!
Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5). Count
ignores blank
cells, so you'll get the total number of cells that have data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have
Excel devide a
resulting value by only the number of cells containing data?
Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum()
function in cell
"U5", however, the total value is now looked at using a
"VLookUp" function
on
another sheet to see what the Tax liability is and places that
value in
cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values, then,
that is
great, all I need to is devide the total Tax liability by
those 12 months
to
get my monthly Tax liability value in cell "H6" - "S6"! But,
what if the
values run from say; "M5" - "S5", can I have the Tax liability
the devided
(calculated) by only those cells in "M6" - "S6"? I need the
formula to look
at the cells in reverse from "S5" back to "H5", see if any are
blank or
empty
cells, trip those off and devide the total value in "V5" or
"W5" by only
those cells placing the results in whatever cells need to show
a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But,
really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

Hi Jay

The answer to your problem is
=IF(H5="","",$W5/COUNT($H5:$S5))

However, I don't think that tax is normally worked out in this way
(certainly not here in the UK). It is calculated each month based upon
the cumulative earning each month, subject to various amounts of "free
pay", then according to rates of tax based upon different income bands.

--
Regards

Roger Govier


"Jay" wrote in message
...
Okay Roger, here goes...

If I take the =Sum() total of H5:S5 (12X1800), I'll get a value of
let's say
21600, the returned value is verified against a lookup tax table using
a
VLookUp function which may say that the TAX liability is 1077.70 in
cell W5.
This returned value would be correct and based on a Income TAX Table.
If I
then took that 1077.70 and devided it amongst the 12 cells ranging
from H5 -
S5, I would have a value of 89.81 for each of the 12 month/cell values
between H5 - S5, placing that value in the cell ranges of H6 - S6,
below the
H5 - S5 income values since the income tax H6:S6 values will be
deducted from
the income values of H5:S5.

Now, what if the person was employed mid year, then, the income range
would
only be populted from say M5 - S5, leaving the other cells L5 - H5
(going
right to left) blank or empty! If I had the formula deviding the value
in W5
over the 12 cell ranging H6:S6, then, I would have a Income Tax value
showing
up in the row below i.e. H6:S6 for which there is NO corresponding
income in
the (Blank) cells on the row/cells above H5:S5.

What I need is a function that will take the value in cell W5, then,
look at
the cells in the range H5:S5 and if all ALL have a value devide the
result in
cell W5 by the 12 cell values, =(W$5/12) in the H6 - S6 cell range. If
only
the cell M5 - S5 have values, then, do a check/count of the cells
above in H5
- S5 and only devide the value of cell W5 by the number/qty of cells
containing a value which in this case would be M6 - S6 =(W5/6), since
H5 - L5
are blank, 0 or have NO value. Basically, I think, it should be a sort
of
IfThenElse. If the cell(s) income above have value, then, count how
many do,
if 12 (all year) have values, devide by 12, if only 6 have a values
(last
half of year), devide by 6 in the H6 - S6 range but only populate M6 -
S6!
Placing those values in their respective corresponding cells below
representing income tax on the values in the cells above/income.

I hope this helps explain what I'm looking for. I Do NOT want the user
calculating any Tax Liability value, I want the function to do it
seemlessly
for them! If the Income cell above in H5 is empty, 0 or blank, leave
the
cell H6 Income Tax like wise empty, 0 or blank. Only place a value in
H6 only
if there is a value in H5...

If there are other questions, please post back! I pretty sure any of
you may
be more confused now than before! I hope NOT! However, if you need
more
clearity, post back?

Oh, a correction, the employee may have either only worked the first
half of
the year or the last half of the year, which ever it was, I'll need to
do a
check againts empty cells in H5 - S5 to decide which cells in H6 - S6
to
place the returned values in!

Thanks for taking the time to look at this crazy one.....




"Roger Govier" wrote:

Hi Jay

either ALL of the cells from S6:H6 going backwards/right to left
(or
half
that number), instead of left to right in row/cells H5:S5!

The direction makes no difference. Take a small example
H I J K
1380 1380 =SUM(H1:K1) = 2760
25 25 =SUM(H2:K2) = 50
1380 1380 =SUM(H3:K3) = 2760
25 25 =SUM(H4:K4) = 50

Fred gave you the answer as to how many values there are
=COUNT(A1:D1) which will return the result of 2 in both cases as
above.

It is not clear from your question, what you want to do with your
result.

--
Regards

Roger Govier


"Jay" wrote in message
...
sSorry, I missed/forgot the most important part. The TAX LIABILITY
value in
W5 get devided into either ALL the cells in H6:S6 or only part/half
the year
as in M6:S6, if only employed half a year.

Thanks once more...



"Jay" wrote:

Okay,..Fred, simple request, here goes...

At times row/cells H5:S5 may contain a value like 1380.00 in
everyone
of the
cells depending on if the employee became employeed the full year.
However,
if he/she started half way into the year, say cell M5:S5, then,
only
about
half of those cells would have a value of 1380.00. The =Sum()
function
generates a Tax value (in cell W5) based on the total! However,
that
total
number of cells containing a value drops off by half for HALF a
year.
So I
need to devide the total tax value by whatever number of cells
have
values,
either ALL of the cells from S6:H6 going backwards/right to left
(or
half
that number), instead of left to right in row/cells H5:S5!
================
Income Example #1
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380 1380 1380 1380 1380 1380 1380 1380
1380
1380 1380
Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00
25.00
25.00 25.00
================
Half Year Income Example #2
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380
1380
1380 1380 1380 1380
Half Year Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00
25.00
25.00 25.00 25.00 25.00
================

I hope this example explains what I'm trying to accomplish!!!

I need to show an Income tax value in the row/cells below in
H6:S6,
which
would be tied to the income values in the cells H5:S5 or only
half.

Than, again Fred....



"Fred Smith" wrote:

It's the other way around, Jay. You give us the example, and
we'll
show you how
to do it. I can't tell from your question what you are looking
for.
Tell us
what's in H5, and what you want to see in H6. It could be as
simple
as
=if(h5="","",yourformulahere), but I can't tell until I know
what
results you
want.

--
Regards,
Fred


"Jay" wrote in message
...
Thanks Fred:

Now, as I attempt to populate the cells below which is row
H6:S6,
how would
I get that to fill in only a value in row H6:S6 or whatever
based
on the
populated cells above in H5:S5? This is where I get totally
lost!
Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5).
Count
ignores blank
cells, so you'll get the total number of cells that have
data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have
Excel devide a
resulting value by only the number of cells containing
data?
Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum()
function in cell
"U5", however, the total value is now looked at using a
"VLookUp" function
on
another sheet to see what the Tax liability is and places
that
value in
cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values,
then,
that is
great, all I need to is devide the total Tax liability by
those 12 months
to
get my monthly Tax liability value in cell "H6" - "S6"!
But,
what if the
values run from say; "M5" - "S5", can I have the Tax
liability
the devided
(calculated) by only those cells in "M6" - "S6"? I need the
formula to look
at the cells in reverse from "S5" back to "H5", see if any
are
blank or
empty
cells, trip those off and devide the total value in "V5" or
"W5" by only
those cells placing the results in whatever cells need to
show
a value from
"S6" back to "H6"?

Is this possible, I know, it sounds crazy, doesn't it? But,
really,...is it
possible?

Thanks, looking forward to hearing back from you guys....

Calculating only non-empty cells...
 

By George, Roger, I think you have it! That is exactly what I was looking
for! It works like a charm! Thank you ALL so very very much for ALL the help
given on this one!

"Roger Govier" wrote:

Hi Jay

The answer to your problem is
=IF(H5="","",$W5/COUNT($H5:$S5))

However, I don't think that tax is normally worked out in this way
(certainly not here in the UK). It is calculated each month based upon
the cumulative earning each month, subject to various amounts of "free
pay", then according to rates of tax based upon different income bands.

--
Regards

Roger Govier


"Jay" wrote in message
...
Okay Roger, here goes...

If I take the =Sum() total of H5:S5 (12X1800), I'll get a value of
let's say
21600, the returned value is verified against a lookup tax table using
a
VLookUp function which may say that the TAX liability is 1077.70 in
cell W5.
This returned value would be correct and based on a Income TAX Table.
If I
then took that 1077.70 and devided it amongst the 12 cells ranging
from H5 -
S5, I would have a value of 89.81 for each of the 12 month/cell values
between H5 - S5, placing that value in the cell ranges of H6 - S6,
below the
H5 - S5 income values since the income tax H6:S6 values will be
deducted from
the income values of H5:S5.

Now, what if the person was employed mid year, then, the income range
would
only be populted from say M5 - S5, leaving the other cells L5 - H5
(going
right to left) blank or empty! If I had the formula deviding the value
in W5
over the 12 cell ranging H6:S6, then, I would have a Income Tax value
showing
up in the row below i.e. H6:S6 for which there is NO corresponding
income in
the (Blank) cells on the row/cells above H5:S5.

What I need is a function that will take the value in cell W5, then,
look at
the cells in the range H5:S5 and if all ALL have a value devide the
result in
cell W5 by the 12 cell values, =(W$5/12) in the H6 - S6 cell range. If
only
the cell M5 - S5 have values, then, do a check/count of the cells
above in H5
- S5 and only devide the value of cell W5 by the number/qty of cells
containing a value which in this case would be M6 - S6 =(W5/6), since
H5 - L5
are blank, 0 or have NO value. Basically, I think, it should be a sort
of
IfThenElse. If the cell(s) income above have value, then, count how
many do,
if 12 (all year) have values, devide by 12, if only 6 have a values
(last
half of year), devide by 6 in the H6 - S6 range but only populate M6 -
S6!
Placing those values in their respective corresponding cells below
representing income tax on the values in the cells above/income.

I hope this helps explain what I'm looking for. I Do NOT want the user
calculating any Tax Liability value, I want the function to do it
seemlessly
for them! If the Income cell above in H5 is empty, 0 or blank, leave
the
cell H6 Income Tax like wise empty, 0 or blank. Only place a value in
H6 only
if there is a value in H5...

If there are other questions, please post back! I pretty sure any of
you may
be more confused now than before! I hope NOT! However, if you need
more
clearity, post back?

Oh, a correction, the employee may have either only worked the first
half of
the year or the last half of the year, which ever it was, I'll need to
do a
check againts empty cells in H5 - S5 to decide which cells in H6 - S6
to
place the returned values in!

Thanks for taking the time to look at this crazy one.....




"Roger Govier" wrote:

Hi Jay

either ALL of the cells from S6:H6 going backwards/right to left
(or
half
that number), instead of left to right in row/cells H5:S5!

The direction makes no difference. Take a small example
H I J K
1380 1380 =SUM(H1:K1) = 2760
25 25 =SUM(H2:K2) = 50
1380 1380 =SUM(H3:K3) = 2760
25 25 =SUM(H4:K4) = 50

Fred gave you the answer as to how many values there are
=COUNT(A1:D1) which will return the result of 2 in both cases as
above.

It is not clear from your question, what you want to do with your
result.

--
Regards

Roger Govier


"Jay" wrote in message
...
sSorry, I missed/forgot the most important part. The TAX LIABILITY
value in
W5 get devided into either ALL the cells in H6:S6 or only part/half
the year
as in M6:S6, if only employed half a year.

Thanks once more...



"Jay" wrote:

Okay,..Fred, simple request, here goes...

At times row/cells H5:S5 may contain a value like 1380.00 in
everyone
of the
cells depending on if the employee became employeed the full year.
However,
if he/she started half way into the year, say cell M5:S5, then,
only
about
half of those cells would have a value of 1380.00. The =Sum()
function
generates a Tax value (in cell W5) based on the total! However,
that
total
number of cells containing a value drops off by half for HALF a
year.
So I
need to devide the total tax value by whatever number of cells
have
values,
either ALL of the cells from S6:H6 going backwards/right to left
(or
half
that number), instead of left to right in row/cells H5:S5!
================
Income Example #1
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380 1380 1380 1380 1380 1380 1380 1380
1380
1380 1380
Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00 25.00
25.00
25.00 25.00
================
Half Year Income Example #2
H5 I5 J5 K5 L5 M5 N5 O5
P5
Q5 R5 S5
1380 1380
1380
1380 1380 1380 1380
Half Year Tax Liability
H6 I6 J6 K6 L6 M6 N6 O6
P6
Q6 R6 S6
25.00 25.00
25.00
25.00 25.00 25.00 25.00
================

I hope this example explains what I'm trying to accomplish!!!

I need to show an Income tax value in the row/cells below in
H6:S6,
which
would be tied to the income values in the cells H5:S5 or only
half.

Than, again Fred....



"Fred Smith" wrote:

It's the other way around, Jay. You give us the example, and
we'll
show you how
to do it. I can't tell from your question what you are looking
for.
Tell us
what's in H5, and what you want to see in H6. It could be as
simple
as
=if(h5="","",yourformulahere), but I can't tell until I know
what
results you
want.

--
Regards,
Fred


"Jay" wrote in message
...
Thanks Fred:

Now, as I attempt to populate the cells below which is row
H6:S6,
how would
I get that to fill in only a value in row H6:S6 or whatever
based
on the
populated cells above in H5:S5? This is where I get totally
lost!
Can you
help explain more clearly by example?

Thanks,...



"Fred Smith" wrote:

Rather than dividing by 12, just divide by COUNT(H5:S5).
Count
ignores blank
cells, so you'll get the total number of cells that have
data.

--
Regards,
Fred


"Jay" wrote in message
...
Guru's

I have a question which may be a brain buster! "Can I have
Excel devide a
resulting value by only the number of cells containing
data?
Examle, I have
row/cells "H5" - "S5" values being calculted in a =Sum()
function in cell
"U5", however, the total value is now looked at using a
"VLookUp" function
on
another sheet to see what the Tax liability is and places
that
value in
cell
"V5" (Single) and "W5" jointly filing.

If ALL the cells from "H5" - "S5" contain income values,
then,
that is
great, all I need to is devide the total Tax liability by
those 12 months
to
get my monthly Tax liability value in cell "H6" - "S6"!
But,
what if the
values run from say; "M5" - "S5", can I have the Tax
liability
the devided
(calculated) by only those cells in "M6" - "S6"? I need the
formula to look
at the cells in reverse from "S5" back to "H5", see if any
are