Forum Question: How Do I Create a Table That Adjusts Data Ranges?

I am using Mac Numbers 4.2. I want to create a table that will show “Yes” when a part change is required every 25hrs but show “No” between 26 and 49hrs then yes at 50hrs but no at 51-74hrs and so on. I know the individual hrs each day and can auto sum these to keep a record of the hours used in total. Some parts require changing at 20hr intervals. I can adjust the formula to serve the individual columns.
—–
Paul Auger

Comments: 2 Responses to “How Do I Create a Table That Adjusts Data Ranges?”

8/28/17 @ 8:39 am

So you have a column that just has the number of hours in it? Like 20 or 25 or 78? Sounds like you just want a set of nested IF statements to figure it out.

Or, is there a consistence between the the intervals? For instance, can you divide by 25 and round down, and if the number is event, then it is No and if it is odd, then yes? Or is it not always exactly 25 (or 20) hours for each interval? If it isn’t exact, or may vary, then nested IF statements with every interval is your only hope.

But perhaps I’m not completely understanding what you want here. I’m OK up until the sentence that starts “I know the individual hrs each day” and can’t figure out what you mean by that.

Another possibility is to use LOOKUP. Read up on that. You can have another table and look up the number of hours in one column, and the YES and NO in the other column. You don’t need to list every integer as it will default to the previous value (49 will default to 25 if the numbers go 0, 25, 50, 75). But if you have different values for each part, then I guess that won’t work.

I think you may need to consult with a spreadsheet expert and have them come up with a solution for you as I suspect there is even more to what you are doing.

Paul Auger
8/28/17 @ 9:02 am

There is no consistency in the numbers. They usually rate between 4 & 8 per cell. These form a running total and when the amount reaches 25 Yes or No will be displayed. I think the LOOKUP will work based on your idea that each integer need not be shown. I did not think of that. Thank you very much Gary.