Can I pay someone to solve my excel problem?

[rkobeyer]

Can I pay someone to solve my excel problem?

Am I allowed to use this forum in this way?

I am just after one elusive formula, I have posted the question all over the Internet with no luck so far and am more than willing to pay someone to help.

I have emailed a number of freelancers with shiny websites but have not received replies yet.

[WizzardOfOz]

I had a brief look around the FAQ and didn't see anything about this. My guess is that yes I'm sure that you would find many experts willing to accept payment :-) However best you e-mail a site moderator for comment, I think both Bob and Ken are moderators here.
is this referring to http://www.excelguru.ca/forums/showt...e-numbers-only or a different problem

[rkobeyer]

I don't know how to email a site moderator :S - I rarely ever use forums. And yes this is about the same problem, to "Spread a yearly total evenly across chosen months using whole numbers only".

[Bob Phillips]

I am sure that there is no objection to commissioning someone, many sites have a specific consultancy facility, and anyone is free to PM the OP if they so wish.

However, it seems unnecessary for what looks like a relatively simple problem (assuming that other thread is the problem). I looked at that other thread, but could not see what the proposed solution was, I saw no formulas.

[rkobeyer]

I am sure that there is no objection to commissioning someone, many sites have a specific consultancy facility, and anyone is free to PM the OP if they so wish.

However, it seems unnecessary for what looks like a relatively simple problem (assuming that other thread is the problem). I looked at that other thread, but could not see what the proposed solution was, I saw no formulas.

The proposed solution wasn't much help, after working all day I think I got close to a solution.

The formula does the maths but comes up with a different total than the original total it used to spread the quantities across the months.

If you look at my attached spreadsheet and enter different values in AF5, you will see what I mean. The 2 totals, T1 and T2 don't always match up so i have to manually fix the error.

It's a half solution that allows me to get by (just). But maybe you can fix it?

[WizzardOfOz]

When a formula looks like that you are looking for trouble.
Quick clarification on spread you have a month and (1) or (E) e.g. D8 and E8 the customer wants 2 items of Product 1 in Dec 2016
and moving over to I8 they want 100 items delivered from (and including June) until end of the year (7 months) (i.e. 5 months of 14 and 2 months of 15)

Also are there production constraints, i.e. if the ideal solution says 1000 widgets in June are they only able to make 500 so need to do this over May and June?

I would also suggest a slight rearrangement in layout so that you can treat it as a database (region,customer, product, year, QTY, spread) that way it will work for new years immediately.
Followed by a pivot table to get your spread summed (region and customer is irrelevant to production, assuming one plant)

End of the day here, will see tomorrow if you are closer to a solution

[WizzardOfOz]

This should do it.

• Extensive use of drop down validation lists
• Orders replaced by a more database type structure. Make sure you copy down (and check pivot range). Can sort and go crazy
• Scaling done once on "ramp" and "even" named ranges. After doing this realised that it could be simplified to one line and a complex if.
• If production exceeds order e.g made 5 but only 4 expected can manually change the month's to 5 and the following months will recalculate
• Pivot table to summarise total
• Pink (bad style) represents formulas, green (good style) headers. I wasn't spending too much time making it look pretty.
• Donations welcome as per thread title, click on wizzard allows you to send a message to that person

[WizzardOfOz]

Ok minor changes to simplify scaling based on if month not started = 0 else..

Then change scaling, if production started this month how would it be ramped schedule(type,month)

Then formula = (total - existing year sum) x ramp
i.e. =IF(MATCH(I$1,Months,0)<MATCH($F2,Months,0),0,ROUND(($E2-SUM($H2:H2))*INDEX(MnthSplt,MATCH($G2,Schedule,0),MATCH(I$1,Months,0)),0))
getting rid of the indirect function and rolling three named ranges (on, even, ramp) into schedule

MonthSpread.xlsx

[Ken Puls]

Can I pay someone to solve my excel problem?

Am I allowed to use this forum in this way?

Just to answer the original question here, by all means you can, so long at that's YOUR choice. We wouldn't permit an expert to hold out on completing a forum answer unless payment was provided, but if you want to pay someone that is your own prerogative.

By looking at this, though, it seems like you may have your answer, so a moot point in this case.