Need expert advice with difficult formula

[leopardhawk]

Trying to piece together a formula that will combine a bit of text with COUNT results from a worksheet called 'payroll'.

Perhaps I should start with what I want the finished result to look like. The formula, which is on a worksheet called 'income_analysis', would return the following:

Average Yearly Payroll for 3 years and 8 months

Remember, this is only an example, the actual data may be much different. Everything in the result is text except the 3 and the 8 which are returned from the other worksheet. To get there, the formula needs to first count the number of cells on worksheet 'payroll' in column/range D12:D300 that have a value greater than zero. Then because payroll is bi-weekly, divide by 26 and convert the result to X years and X months as shown above (rounded off of course).

This is what I have so far but I need to add the text around it and also convert it to years /months.

=COUNTIF(D12:D300,">0")/26

I hope this makes sense and that there is a relatively easy solution. Thanks!

 

[SpillerBD]

The values in D12:D300 are dates, amounts or other?

Is there a column with the pay dates?

 

[storm925]

maybe something like this...

for D12:D300 I have 289 rows...

=COUNTIF(payroll!D12:D300,">0")   ...gives me = 282

and

=COUNTIF(payroll!D12:D300,"=0")   ...gives me = 7

and

=COUNTIF(payroll!D12:D300,">0")/26   ...gives me = 10.84615


so maybe something like this...

="Average Yearly Payroll for " & INT(COUNTIF(payroll!D12:D300,">0")/26) & " years and " & ((B8-TRUNC(B8)) * 26) / 2 & " months"

...gives me = Average Yearly Payroll for 10 years and 11 months

Hopefully this is what you are looking for.

--Ben

 

[SpillerBD]

The values in D12:D300 are dates, amounts or other?

Is there a column with the pay dates?

If you have Dates, you can have a simply

=Max(E12:E300)-MIN(E12:E300)

& Format the cell as a custom format of

"Average Yearly Payroll for "y" years and" m "months"

The downside to this route is your result always has and only has two digits for years.
Upside may depend on your data, but you always have the correct time calculation in case there might be additional rows beyond standard payroll checks.

Note, a Table would be better source to allow table referencing which is more flexible.

 

[leopardhawk]

storm925, thanks for your efforts, I am getting a #VALUE ! error when I use your suggestion in the worksheet. What is the reference to cell B8?

SpillerBD, The data in column D is a dollar amount. Users may be entering dollar amounts in column D every two weeks when they get paid. My understanding is that the COUNT function simply counts the cells that have anything in them. If there are 100 cells in column D that have an amount in them, the formula should return 100/26 which equals 3.85. In this case, I need the formula to return the following:

Average Yearly Payroll for 3 years and 10 months

Hope this helps!

Cheers

 

[storm925]

Sorry, I copied the wrong formula when testing it, here is what I have...

="Average Yearly Payroll for " & INT(COUNTIF(payroll!D12:D300,">0")/26) & " years and " & ROUND(((((COUNTIF(payroll!D12:D300,">0")/26) - TRUNC(COUNTIF(payroll!D12:D300,">0")/26)) * 26) / 2), 0) & " months"

In your last example, put the following in cell A1, =100/26

="Average Yearly Payroll for " & INT(A1) & " years and " & ROUND(((A1 -TRUNC(A1)) * 26) / 2, 0)

In cell A1 if you enter, =98/26, you get the 3 years and 10 months

is this what you are looking for?

 

[storm925]

Just one more thought, if you replace ROUND with ROUNDDOWN, 99/26 will get you 3 years and 10 months. This is if you are looking for completed months in your calc or more of a conservative calc. Have a great day!

 

[leopardhawk]

storm925, I changed both of the B8 cell references in your formula to 'payroll!D12:D300' and now the #VALUE ! error is gone, no errors at all. In fact, what I have in the subject cell is 'Average Yearly Payroll for 0 years and 0 months. The problem now is that it is staying at 0 years and 0 months, even if there are dollar amounts in column D. So it appears that the formula is not actually counting any of the cells in column D.

Any idea as to why? Thanks!

 

[leopardhawk]

We were both typng at the same time... I will try your latest suggestions and get back to you! Thanks!

 

[leopardhawk]

Okay, testing is complete and the formula you suggested with the ROUNDDOWN in it is working perfectly! Thank you so, so much, you're a lifesaver!

Cheers!