If statements

[jame24]

Hey Guys,

I have a model that predicts supply based on 3 factors 1) Month of year, 2) day of week and 3) time of day

Currently the model is set up so that from a table (g1) in the model if(a1=sunday,g1,if a1=monday,g2 etc) i have this formula set up for each hour of the day so it puuls supply based on that hour of the day given the day of the week.

The problem i am having is adding in the month of the year as the formulas get very very messy and very long. Is their an easier way to add in the month as oppose to using a general if statement (e.g. if(and(a1=january,a2=sunday,b3,if(and(a1=january,a2=monday,b4

This is what I have now and I have to that for 7 days a week 12 months a year and 24 hours a day. Thats over 2000 different combinations. Is there an easier way to create this that i may be missing.

thanks in advance

[ConneXionLost]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jame24]

Here is a version i am working on that does not include the months

Cheers

[ConneXionLost]

What impact/change do you expect the month to have on your (G1) table?

[jame24]

I'm going to/want to have a table like that for every month since the given % based on day of week and hour are different for every month so january on a tuesday at 1 will have a different % then november on wednesday at 1.

[jame24]

Hey, I have attached an updated version of the model to make it easier to understand what I'm trying to do

Cheers

[ConneXionLost]

If so, then you might as well build your lookup table for an entire year, and use dates (in column H) instead of days of the week.

[jame24]

hey thanks, how would i do the lookup with multiple criteria? i need to look up if its a month, then lookup the day then the hour.

Cheers

[ConneXionLost]

Try this:

Cheers,

[jame24]

Hey Man,

Thanks a lot,That worked brilliantly. Ill try and do that for the other months and let u know how it goes. Thanks a lot!

Cheers
James

[jame24]

Hey man,

I am trying to create a model to forecast demand on any given day of the week based on the month of the year based on a given hour of the day.

The model shows how much expected demand for any given hour is and what the production needs to be to meet the demand. (this i have managed to do)

the problem i am having is that production is dependent on a few things:
1) we can only make 9 units at a time (i have factored this into the model)

2) the number of units can only stay good for 3 hours after which point the product is useless - i am having difficulty factoring this in

3) we need to minimize over production. - This is where i am having the most problems.because production is only in 9's the over/under production is highly variable and i was wondering if their is a way to either reduce the variability or try and ensure that overproduction is between 1 and 3% - i have included a data table that shows the high level of variability

4)demand must be met and cannot change

I have also attached the model I'm working on and any advice or suggestions you have would be much appreciated.

ps i posted this on the forum but can't seem to delete it sorry

Cheers

[ConneXionLost]

Best to mark this thread as SOLVED and continue with the new question in the other thread. I've annotated your other thread to reflect this.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save