Need expert advice with difficult formula

leopardhawk

Active Member
Joined
May 31, 2007
Messages
451
Office Version
  1. 2016
Platform
  1. 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 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.

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

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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
The values in D12:D300 are dates, amounts or other?

Is there a column with the pay dates?
 

storm925

Board Regular
Joined
Jan 20, 2010
Messages
226
maybe something like this...

for D12:D300 I have 289 rows...

Code:
=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
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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
Code:
=Max(E12:E300)-MIN(E12:E300)
& Format the cell as a custom format of
Code:
"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

Active Member
Joined
May 31, 2007
Messages
451
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 20, 2010
Messages
226
Sorry, I copied the wrong formula when testing it, here is what I have...

Code:
="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

Code:
="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?
 
Last edited:

storm925

Board Regular
Joined
Jan 20, 2010
Messages
226

ADVERTISEMENT

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

Active Member
Joined
May 31, 2007
Messages
451
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
May 31, 2007
Messages
451
Office Version
  1. 2016
Platform
  1. Windows
We were both typng at the same time... I will try your latest suggestions and get back to you! Thanks!
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
451
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,108
Messages
5,545,986
Members
410,718
Latest member
ALM1GHTY
Top