leopardhawk
Active Member
 Joined
 May 31, 2007
 Messages
 451
 Office Version

 2016
 Platform

 Windows
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 biweekly, 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.
I hope this makes sense and that there is a relatively easy solution. Thanks!
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 biweekly, 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.
Code:
=COUNTIF(D12:D300,">0")/26
I hope this makes sense and that there is a relatively easy solution. Thanks!