create a build schedule (for panels) based on quantity/day

I'm having a little trouble visualizing what you're after. I think I'm close, but am missing a few details.

First, the easy one: Change C7 to be a simple number - 3.

Having "3 panels per day" makes it much harder to calculate since you have to extract the number 3 from the surrounding text. If this was just '3' (and maybe 'panels per day' was in cell D7 for legibility), life would be so much easier :)

Moving on, I think what you're saying from this spreadsheet is that you need 8 panels total (C8:C15). With a limit of 3 per day I think you want to highlight the first 3 panels as being made on Monday, the second three panels on Tuesday, and the remaining 2 panels on Wednesday, is that right?

If you somehow up production to 4 panels per day, you want 4 panels on Monday, 4 on Tuesday and 1 on Weds. Similarly, if production drops to 2 per day you want 5 days mapped out.

Leading on from that, how does the formula know the start dates? If you have another job in production and can't start this job until Weds, how does the sheet know that? (or will you change the date in row 4 to accommodate that?

What's the longest job duration you're likely to encounter? I can see a couple of paths forwards, but the formulas could get unwieldy if they span across weeks of production time.

Lastly, for the moment, I think you have a misunderstand of the IF()/AND() functions in your example.

IF() takes three arguments:

• a conditional statement that returns a true/false result
• a result to return (or calculate) if the conditional is true
• a result to return (or calculate) if the conditional is false

In your example, you have:

=IF($C8="",AND(E8),0),0)

This can be broken down into:

Conditional: $C8="" - this is fine... is $C8 empty?

True: AND(E8,0) - this makes no sense. If $C8 is empty, then return the result of AND(E8,0) which will always be false since you're trying to AND() anything with 0

False: 0 - this is OK... if $C8 is not empty, return 0

If you want to check if two conditions are true (which is my guess here), it would be written more like:

IF( AND( <condition 1>, <condition 2>), <result if true>, <result if false>)

This runs a check for condition 1 and condition 2, if they both return true, it returns <result if true>, otherwise if returns <result if false>.

However, I don't think that's the crux of the issue... but answering some of the above questions will help shed some light and move forwards.

OK, Never mind... I made a few assumptions and took a deeper look at this and I think it's simpler than I first thought.

The key is really working out which production day any given panel should be made on. That's actually easy to do with a little math:

=ROUNDUP($B8÷$C$7,0)

Where $B8 is this panel's index number, and $C$7 is the number of panels per day. Round this up and we have a simple calculation to identify which day any given panel should be built.

The second trick is working out the production days. I went out on a limb and assume that you'll set the first production day manually (based on existing orders in flight).

On that basis, I'm assuming you enter the start date in E3

F3 can simply be:

=E3 + 1

which increments the day by 1

In row 4 I added a DAYNAME() function to calculate the day of the week for the given dates.

There's one extra step - you don't work on weekends, so we need a way to flag which days are work days vs. non-work days. I did this with an additional row 5, where E5's formula is:

=IF(WEEKDAY(E3,3)<5,1,0)

This uses WEEKDAY to return a day of the week, where Monday is 0. Therefore any value 0..4 is a weekday, and 5 or 6 are weekend days. Now I have a series of 1s and 0s identifying week days.

This row can be hidden if you want, but it's value will become apparent soon.

So now we have a way to determine which production day any given panel is scheduled to be built on, and we have a track of which days are production days. Time to tie it together.

The formula in E8 starts off as:

=IF( ROUNDUP($B8/$C$7),0) = SUM($E$5:$E5)),1,"")

This first calculates this panel's production day (based on simple division), and compares it to the sum of production days so far (based on counting values in the Production Days row). If they match, this panel is supposed to be built on this day, so we return 1, otherwise we return 0.

Now, we just need to add to this a check as to whether today is a production day or not. For that, we AND() the ROUNDUP() with a check for this column's production day:

=IF(AND(E$5=1,ROUNDUP($B8÷$C$7,0)=SUM($E$5:E$5)),1,"")

this checks if E$5 is 1 (today is a production day), AND this panel's production day (based on division) matches the number of production days on this job.

Add one more check to the AND() to make sure we have a panel that needs building, and we're set:

= IF(AND($C8≠"",E$5=1,ROUNDUP($B8÷$C$7,0)=SUM($E$5:E$5)),1,"")

Fill this one formula across the columns for all the days, and down the columns for all the panels in the order and you should be set.

Now this will automatically let you set any start day in E3 and it will work out the other production dates (excluding Sat/Sun.

Change the number of panels per day in $C$7 and the schedule will update:

Hope that helps

> (sorry, I think there's probably a time difference, hence I didn't respond to your first post!)

Oh, don't worry about the time thing - it was only a couple of hours between my posts and I'd just been thinking about how to streamline the results :)

> You've added in a feature I wasn't really looking for, but totally going to use!! I've just used checkboxes though (so it's easier to select/deselect) and then as you suggest, I'll hide row 2.

I just made an assumption on working days, and that was my first thought. If you're happy to manually mark days on/off production, that's great (and adds flexibility around things like days off, etc.

I came about that because I couldn't assume your work schedule would always start on a Monday. If a job run was to start on, say, a Wednesday, then there would be only three days of production before the weekend and therefore the fixed grid (5 days on, 2 days off) would break.

If you are using checkboxes you can actually streamline the main formula a little, and nix the hidden row - you're already comparing the checkbox (I$3=true), and you can replace the SUM() of 1's with a COUNTIF() to count how many checkboxes are set:

=IF(AND($C8≠"",E$3=TRUE,ROUNDUP($B9÷$C$8,0)=COUNTIF($E$3:E$3,TRUE)),1,"")

This counts how many checkboxes are TRUE(), and therefore doesn't need the row of 1s and 0s.

> Although it's a little academic now, to answer your question about the number of panels and the text, I set this up using Custom Format, so it's just a number (## panels per day).

That's a perfectly acceptable way of addressing that. Many paths to the same result :)

> I've now realised I've backed myself into a corner now though with working out how many panels of a certain type I need to make, but I'll try and see if I can figure it out, otherwise I'll ask on this forum again later...

Ahh.. the never-ending quandary of 'just one more thing...' :)

Anyway, glad I could help.