Expressing multiple values in one cell while appearing as a number range (I.E. 20-24)

[North80]

Hello all,

I'm new here and fairly new to Excel. This is my last resort for help on a issue that I can't seem to solve in my Sleuthing of the dark reaches of the interwebs. I appreciate any help you might be able to offer. Thank you in advance. :-)

So let me get to it. What I'm trying to do is build a spreadsheet where a number range is visible in a single cell(Example: 20-24), while representing all the values in between so that they can be added in various ways in another cell. So a cell that shows 20-24 would contain the values of 20,21,22,23,and 24 while only 20-24 is visible. This spreadsheet will contain multiple cells in a single column with different value ranges such as 20-24, 8-16, and 14-24.

My next dilemma is that once the cells are formatted correctly, I need to be able to find how often a singular number or number range occurs in a single column. So if a column contains multiple value ranges such as 20-24, 8-16, and 14-24, I need to find out how often the value range of 16-18 occurs in that column and attribute a total for that in a single cell at the bottom of the column. So if the values of 16,17, and 18 occur in a single cell, that would count as 1.

Truthfully, I don't know enough about this stuff to even know if all this is possible or if there are better ways of going about it, but I figure it's worth a shot. This is for a spreadsheet for my job managers to simplify scheduling. Thank you for any help guys.

[AliGW]

Welcome to the forum!

What you are describing is a recipe for disaster. There is almost certainly a better way of doing this that will not cause you headaches with your data analysis down the line, and it will start with a normalised data layout. In order to offer more precise help, you'll need to provide some sample data.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[jason.b75]

My next dilemma is that once the cells are formatted correctly

That is the wrong way to create a spreadsheet, you should work on functionality before formatting.

Having 2 numbers in one cell will never work properly. It would be better to have lower and upper limits in separate cells, then work with that.

Using this as an example,

if a column contains multiple value ranges such as 20-24, 8-16, and 14-24, I need to find out how often the value range of 16-18 occurs in that column and attribute a total for that in a single cell at the bottom of the column. So if the values of 16,17, and 18 occur in a single cell, that would count as 1.

Enter 20 into A2, 24 into B2, 8 into A3, 16 into B3, 14 into A4 and 24 into B4.

Then you can count the rows that contain 16 to 18 with =COUNTIFS(A2:A4,"<=16",B2:B4,">=18")

You might need a variation to the formula if you want to count overlaping ranges (i.e. if a count of 14 - 22 should equal 3 in the above example).

[North80]

Hello Ali,

Thank you for your warm welcome and response. As you already have gathered, I could use some help as to what the best way to go about this is. Thank you. I'm including a sample that represents the type of data that I need to be able to modify and sum up at the bottom. The bottom numbers represent the number of times those value ranges occur in the individual cells above. You'll notice some cells have two value ranges in them. This no doubt complicates things a little bit. Thank you for any help or advice you can offer as far as what the best way to go about this is. I'm hoping the sample uploaded correctly.

[North80]

Thank you Jason. I have uploaded a sample of what I'm trying to articulate. I have no doubt that you see the solution better than I do. Thank you for your suggestions. I think if I understood how the formatting works, I would better understand how to set it up with some semblance of functionality. I'm still learning about SUMIFS and COUNTIFS and how to use them. Thank you for any further advice you may have.

[AliGW]

This is really poor data layout. You are setting yourself up for a complete nightmare here! Can you share what the underlying data looks like? In what format is it collected?

[North80]

Yeah, I figured it would be. This data is for scheduling, so it's not collected. It simply represents hours in a given day in 2400hr format and the counts at the bottom represent coverage of those hours. I'm trying to lay it out so the hours can be modified and the counts can be totaled at the bottom. Not too sure what the best way to do that is. What layout do you think would work better? Thank you Ali.

Jack

[AliGW]

You aren't yet articulating clearly (for me at least) exactly what the raw data represents. What I will say, though, is this: data capture is not something that you should do by groupings. Data should be captured in a normalised format - a single row in your data for each entry, which might have a time and/or date stamp - and then you create a report based on that data (either using formulae or Power Query or VBA) to display that data in a user-friendly, grouped format. I hope this makes sense.

[North80]

Yeah, you lost me a little bit in there. I have some homework to do for sure. So the individual columns each represent one given day, and the data in the individual cells represent hours worked or covered. So if a cell says 16-24, that represents 1600hrs-2400hrs. The idea is to be able to list all the hours of work covered in a given day and tally those in the bottom counts. On the bottom, where it says 6-7, 7-8, 8-14, and so on, that's a representation of how many people are working during those timeslots based on the hours covered in the column above it. I hope this better explains what I'm trying to do and represent. I'm not sure I know what terminology to use, but I hope the essence of what I'm trying to do comes through and will give you enough of an idea to suggest the better way of doing this. Thank you for your suggestions and patience Ali :-)

Jack

[AliGW]

OK - so what you need is a register. Presumably your staff clock in and out, so you would have a data table that lists each clocking in and clocking out event:

Staff ID - Date - Clock In/Out Flag - Clocking Time

This is your normalised data collection. From this you create the view you are looking for (which is really just a visually more informative representation of the base data, but the base data itself needs to be in the simplest format possible).

I hope this makes a bit more sense. Essentially, your starting point is never going to be grouped data.

[North80]

The idea is to use this to develop scheduling by calculating coverage automatically instead of having to go through each individual cell and add the coverage manually. This is what's been done, but I think there's a better, faster way of doing it. Your breakdown is pretty much spot on. This is more of a visual representation of the data so it can be read easily. I'm not too familiar with a register or how to build one though. I think my main problem right now is figuring out how to lay out the values so that Excel can then understand how to count or sum up the hour ranges I'm trying to calculate automatically. Thank you again Ali.

[jason.b75]

Are you using excel 2016 as it says on your profile, or 2003 as suggested by the use of the xls file extension?

Have a look at this edited version of your sample, which has each number in a separate cell as was suggested earlier.

Looking at the entry, 0-8, which was originally in C1, you will see that you now have 0 in C1 and 8 in D1, with the - applied by using a custom format.

The entries at the top where you had 2 ranges in 1 cell have been split into 4. This is the only practical way to make it work.

[North80]

I'm using 2016. I think the original file is an older file extension. What is the current 2016 extension?

That is awesome Jason. That's essentially what I was working on when you formatted yours. Let me show what I've got so far. The part I'm still trying to figure out is the formula, but you're getting dangerously close to a solution to my issue. Thank you Sir. Let me know what you think. I suspect you can code the formula for this in a heartbeat.

Jack

[jason.b75]

Check the bottom part of the version that I re-formatted earlier, Jack. The formulas are already there

All versions of excel from 2007 onwards use xlsx file extensions (xlsm is it contains vba / macro code).

Old and new files are cross compatible, but we just have to be careful with formulas, for example the COUNTIFS function that I used in the sample file will not work if you open the file in excel 2003, we would need to replace it with a sumproduct formula instead.

[North80]

Check the bottom part of the version that I re-formatted earlier, Jack. The formulas are already there

All versions of excel from 2007 onwards use xlsx file extensions (xlsm is it contains vba / macro code).

Old and new files are cross compatible, but we just have to be careful with formulas, for example the COUNTIFS function that I used in the sample file will not work if you open the file in excel 2003, we would need to replace it with a sumproduct formula instead.

That's excellent! I've been tinkering with them and trying to understand just how they are working. I think I have the gist of it :-) I do have a couple questions though in understanding the formula. In your workup that you made, would it be possible to include the formula you have in E29 to C29 so that it combines the two to make 6. I tried to do it myself but I just don't understand enough about the code to do it without errors. Now at the risk of showing just how much I don't understand about this, here's what I did to try to accomplish that. "=COUNTIFS(C$1:C$21,"<="&$A29,D$1:D$21,">="&$B29,E$1:E$21,"<="&$A29,F$1:F$21,">="&$B29)" If you could give me just a basic breakdown of the formula you wrote, that might help me better understand how these things work together. Thank you for your help and your time Sir; I really appreciate it.

Jack

Jack

[North80]

I think I may have figured it out. Is this the best way to accomplish adding two COUNTIFS together? =COUNTIFS(C$1:C$21,"<="&$A29,D$1:D$21,">="&$B29)+COUNTIFS(E$1:E$21,"<="&$A29,F$1:F$21,">="&$B29)

[jason.b75]

That is the only way to do it, Jack! Good to see you figured it out

There are other ways, such as =SUM(COUNTIFS(criteria)) but that would be for counting, for example all of the 7-8 and 17-18 ranges in a pair of columns, but ignore the rest of the time ranges in between.

[North80]

That is the only way to do it, Jack! Good to see you figured it out

There are other ways, such as =SUM(COUNTIFS(criteria)) but that would be for counting, for example all of the 7-8 and 17-18 ranges in a pair of columns, but ignore the rest of the time ranges in between.

Excellent advice Sir. With your suggestions I was able to build exactly what I was looking for and it works incredibly well. I do have one more thing that I'm trying to figure out though if you wouldn't mind. So I can total the number of people working in the specified time slots, but I'm trying to total the number hours represented in the horizontal row, in a given week. So if we look at your workup example, from C$1 to P$1, the hours represented there would total 44 hours. How would a person total the hours represented in the entire row? Thanks again.

Jack

[jason.b75]

Give this array formula a try

=SUM(IF(MOD(COLUMN(C1:P1),2),-C1:P1,C1:P1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Or the easier way, if you insert a new line at the top, then enter a heading such as "Start" into C1, E1, G1, etc and "Finish" in D1, F1, H1, etc then you could use a regular formula like

=SUMIF(C$1:P$1,"Finish",C2:P2)-SUMIF(C$1:P$1,"Start",C2:P2)