Formula based on Day of the Week

[PaulThomas]

I am looking to create a formula, which is associated with the value in a cell.

So going from A1 to A7, you would have:

Fri Sat Sun Mon Tue Wed Thu

Under each day, I would like to return a value, based on the day. So for example:

If A2 = Fri, the value would be 200
If A2 = Sat, the value would be 400
If A2 = Sun, the Value would be 450

...and so on. So I guess I would need a formula that held 7 conditions to cover each day of the week. Cells A1 to A7 are also formatted as "ddd" and based on another cell in the sheet. Not sure if that is causing issues.

Any help would be appreciated!

 

[Michael M]

Maybe this and copy down

=LOOKUP($A$2,{"Fri","Sat","Sun","Mon","Tue","Wed","Thur"},{200,400,450,500,600,700,800})

 

[PaddyD]

or, but only if there was a regular relationship between days of week and value incrememnts in the scale, you could take advantage of fact that for a date that's a monday:

=weekday(a1)

...equals 2

...in formulas of the form =weekday(a1)+(constant*(formula))

 

[PaulThomas]

Each day of the week will have a different value, based on criteria. So there is not pattern to play off of.

The formula from Michael M did not work but it looks like it is on the right path. The days will jump over one cell each day, which is why I wanted to have this formula in under each day, to have the value linked.

But I thank you both for the input. With any luck, I will get there.

 

[Michael M]

Paul.
Ok, if my formula did not work, can you give me a clue as to why it didn't !!
Then maybe I can adjust it to suit.
Your explanation isn't quite clear enough.
Have a look at the 2nd line of my tag to download ExcelJeannie and then you can post
a screen shot of what you have ....and what you would like as a result

 

[PaulThomas]

Actually, that seemed to work. I had to convert the "ddd" cells to text and then it worked perfectly.

Thanks again!!!

 

[Michael M]

Excellent....glad you got it sorted !!