Percentage Count

[RandyD123]

I want to count a range like A9:JU1. In the range I will have data: A, B, C, D, RDO, X, P. That data is the ONLY choices I can make. I want to figure out (in cell A4) the percetage of the times I use "A"

And in cell A5 the percentage I use "B"

In cell A6 the percentage I use "C"

Cell A7 the percentage I use "D"

Cell A8 the percentage I use "P"

I don't want to count "X" or "RDO" in any of the cells (A5, A6, A7, A8)

I don't want to count blank cell across the range (A9:JU1)

How do I make a fomula to make this work!!

Thanks for any help in advance!!!!

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[RandyD123]

sample included hope this helps....password (Secret) no parentheses

[RandyD123]

So heres what would happen if done right. Lets say I just started this sheet brand new. All the counting cells are at "0". The first time I'm at "B" the B count would be 100% If I then go to "C" the next day the counting cells would be 50 50. If the next day I'm at "D" then the counting cells would be 33 33 33.....

[newdoverman]

If you are not counting X, RDO and Blanks, the example in the attached file calculates the % that A B C D are used (not counting X, RDO and Blanks)

[RandyD123]

Almost but it need each row to be independant of each other. The yellow cells will be where the math happens! I might not need the %D column...if all works out

[newdoverman]

Is this the calculation that you want? Enter in E8 and copy across and down.

Please Login or Register  to view this content.

[RandyD123]

I think it is!!! I had to add *100 at the end of the formula. I do get a divide by zero error though if all the cells are empty or they ONLY contain on "X" "RDO" in the row

Please Login or Register  to view this content.

[newdoverman]

Ament the formulae by adding =IFERROR( at the beginning of each formula and end the formulae with ,"")

This will result in blank cells instead of errors.

Please Login or Register  to view this content.

[RandyD123]

SWEET!!! It works perfectly.....I hate to ask, because it's really not needed AND I plan on deleting the %D column BUT!!!....if my row is blank I get a #DIV/0! error!!!

[newdoverman]

This incorporates the IFERROR and instead of multiplying by 100 I used % format. (your choice of which to use) I also added the IFERROR TO H7:H9

[RandyD123]

Thank you very much!!!!

[newdoverman]

You're welcome.

Thank you for the feedback.

[RandyD123]

So I added a couple of more possible "locations" to be counted. The criteria is if someone is at "A" then they go to "D" we fill the cell as EITHER "A/D" or "D/D". These need to be counted as 1 instance at "A" AND 1 instance at "D".

I have all possible combinations showing in the sheet, and conditional formatting will take care of "color".

I don't know how to add or change the formula to take make it work if someone was at "A/D" or "D/A" or "B/D" or "D/B" or......

I have attached the file again with what works. I am assuming I need a "countifs" formula but when I do that it returns all "0's".....

Right now I added a "Split Count" column because I wasn't sure how to do it correctly. The "Split Count" column will get deleted if I get what I want to work!!!

I have considered a hidden row right under the A,B, C D row...

Protection (Secret) without parenthesis

[newdoverman]

This has suddenly become very, very confusing.

This is a huge difference from the original problem. Is this the end of the requirements or is there more? It is better to know now rather than later.

Here is a simplification of the formula that you have in the "Split Count":

Please Login or Register  to view this content.

Here is also a simplification for the formula in D7 to copy across and down:

Please Login or Register  to view this content.

[RandyD123]

SORRY, but yes it is at the end. We were not going to "track" a person who was at multiple locations on any given day. We were just going to assign them one value for the entire day eg: A, B, C or D. We found it nessassary to count them at each location so they would get credit for the time spent at EACH location. Because you have provided a working code that uses percentages I don't think it would matter that they get counted twice in one day.

If I want to see who had more time at "A" vs "D" it would still be accuate because if I spend one day at "A" and then one day at "D" I get 50/50. If the other person spent 1/2 day at "A" and 1/2 day at "D" they get 50/50 as well. If the next day that other person is JUST at "A" then they have 2 instances at "A" and one at "D", the percentage for them, over the 2 day period would be 33/33/33 (A/D,D) mine would be 50/50 (A, D)

I hope this helps???

[RandyD123]

Thank You for all the help!

[newdoverman]

You're welcome.

The reason that I asked about the revisions is that a seemingly small revision can result in a very large change in the formulae for the solution.

I had a rush of "crap" to the brain and I think that I found the solution.....I hope

[RandyD123]

I think after the +SUM I have to delete some of the combos after the /counta the first part looks ok but the second have should look like the first... And in the "P" column I don't think I need any of it because there is NO split with "P" ever???

The "D" column looks perfect.

After trying my revision the math don't always add up to 100. I think I need your version.......yours works PERFECTLY!!!!

I thought I could revise the A, B, C columns because in the A column it would NEVER count a "B/D" but I see it does need to calculate a percentage regardless

[newdoverman]

All columns will have a different numerators and denominators because of the things that have to be counted that are specific to the P,A, B, C, D category columns. Without the numerators and denominators being specific to the category, the math will not work out at all.

It took a bit of work to get all the rows to total 100%. The correct accounting for the splits determined if the formula would work or not.

The formulas can be copied down the columns but NOT across.

[RandyD123]

COPY THAT!!!! Thank You Very Much for all your help.

[newdoverman]

You're welcome

Thank you for the feedback.

[RandyD123]

I'm not sure if you noticed or not but there are 2 separate sheets that I've been working on. One is an "AM" sheet (the one with the splits) and a "PM" sheet, no splits on that one. It looks like the "AM" will add to 100% going across all the way down. I don't think my "PM" sheet does that. I"m not at work so I cant post it with revisions but maybe we can look at it next week sometime?

I'll post both tabs for a final review and revision. I wish I could give you another "reputation" point but the forum won't let me!!!! You've been a great help and I truly appreciate it.

[newdoverman]

If the PM worksheet is virtually a copy of the AM worksheet (cell references being the same), there should be little difficulty in copying the formulae into the PM worksheet and have everything work as these formulae are not dealing with time but text entries in cells.

If there are no splits in the PM worksheet, the splits should be ignored in the formulae...no need to do anything.