Adjusting a Formula based on date of week and time of day

[js10053]

Hey,

I'm hoping someone can help me with this, I know what I am trying to do, but I can't seem to figure out how to make a formula that will understand my thinking. This is what I need to do:

I want to figure out what percentage of sales is needed based on the day of the week, but also based on the time of day. I currently have a sheet set up that has the percentage I need to be at for each hour, each day of the week, that is separate from the page where I have my goals for the day. So for example, let's say I need to make $10,000 for the day and it is Thursday at 4:00 PM so I need to be at half of my sales, or $5,000. But, if it were Friday at 4:00 PM I only need to be at $3,500 at that time. But the percentage will vary each day and each time of day. All of the percentages are calculated up and ready, I just need to be able to somehow figure out how to get the file to figure out what my goal should be based on the time and day. Is it possible to create a formula, or perhaps even something I'm not thinking of, or will I need to actually go in and say the day of the week and time for it to pull the information?


Hopefully that all makes sense, I appreciate any help you can give!

 

[silentwolf]

Re: Help Adjusting a Formula based on date of week and time of day

Hi,
it would help to have some examples of how the data is organised in your sheet and what calculations you like to get in order to help.
I am thinking of a custom function but as I said you should provide examples of how the calculations need to perform.

 

[js10053]

Re: Help Adjusting a Formula based on date of week and time of day

Sorry, I was going to say that I can adjust to, however, it needs to be done if it makes it easier. I don't really have any example yet as I haven't gotten that far in the process. I sat down to try and figure out the formula and couldn't think of where to begin. But basically, I need to take a budget and figure out the percentage I need to be at. The budget is predetermined and the percentage is as well, I just need the file to take the time and day of week and multiple the budget by the percentage to get to the goal.

I can go into more details, but as I mentioned, I haven't' gotten that far as I was thinking it may be easier to work around the formula than to make the formula work for me. Since I was just trying to get the basics of the file going, I currently have one tab that has my working information to figure out where my sales are, budgets, etc. and then I put tables on separate sheets that have the break down of where I need to be based on the hour and day of the week. But again, this is all adjustable, so if you have a suggestion for a custom function, as you mentioned, or something else I should try, I can try and organize the file to work around that.

Hopefully that clears things up, if it is more helpful I can provide more information.

 

[js10053]

Re: Help Adjusting a Formula based on date of week and time of day

I worked on this some more today to play around with it. I was able to try several different formulas that I thought I may be able to put together to get the information, but I think it will make it too complicated and wasn’t able to get the end result anyway. So I’m going to attempt to explain this a little better.

On any given day I have a set goal that I need to reach. Based on every day of the week and each hour, the percentage I need to make in order to hit that goal will vary. I’m trying to get a formula that will allow me to simply multiple that daily budget by the percentage I should be at to get my goal for whatever time I open the file. I have tables set up where the day of the week goes across the top, each day in its own column, then the hours are represented in the rows. What I need to do is be able to find a value from within the column and rows, for example Thursday at 14:00 , which would then return the value that is in that cell.

Once I have that value I can then multiply it by the goal with a formula to see where I need to be, but the problem I am having is to try and figure out how to get excel to look at the two different values, i.e the day and the time and then return that value. I have looked at several different options, indexes, sumif, match, etc, and I feel like something should be able to be utilized, but I can’t seem to put the pieces together to figure out the puzzle. If someone can help me with how to have excel find that data, I think I can figure out the rest. I do have the file showing the date and time, ideally I would like the file to automatically look at that info to determine what it is looking for, but if I need to I can do a drop down to select the time and get the information. Like I said, I think if someone can help me to figure out how to get the file to pull the cell data I need, I think I can figure out the rest.

Let me know if I need to clarify anything.

Thanks again!