1

I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using Google Sheets by the way)

I want the cell underneath the day of the week that has been calculated, to display a number (which is the number of stylists in the salon on that day usually) on that day of the week. i.e if the cell above says 'Tues' then to display '5' and if it says Weds to display '4' and so on....

So row 1 is date, row 2 is day of the week (calculated by the date in row 1) and I want the cells in row 3 to display the number of my wishing dependant on the day in row 2. I hope this makes sense? I tried the 'if' formula but couldn't seem to master it!

It won't let me add images, so here is the link to the master sheet: https://docs.google.com/spreadsheets/d/1wqtMktTHcc1ldztyI_ctu58nPxRiUt6owaoOh1_KzjM/edit?usp=sharing

migrated from superuser.com Sep 15 '15 at 18:53

This question came from our site for computer enthusiasts and power users.

  • 1
    Show what you have so far and what it's giving you vs what you want – Raystafarian Sep 15 '15 at 17:43
  • 1
    It'd be great if you were to edit your question and put a snippet of sample data to help illustrate what you're trying to do. – ale Sep 15 '15 at 19:40
  • sorry but I am a complete novice at spreadsheets so I can't be much clearer than copy the document and make it available! Sorry to be a pain.... – Gavyn Sep 16 '15 at 16:43
2

This can be solved by introducting a separate sheet, let's call it 'Weekdays':

| Weekday | Number of stylists Site 1 | Number of stylists Site 2 | Number of stylists Site 3 |
|       1 |                         0 |                         0 |                         0 |
|       2 |                         0 |                         0 |                         0 |
|       3 |                         5 |                         3 |                         5 |
|       4 |                         4 |                         4 |                         4 |
...

The first column is the weekday (0 is Sunday, 1 is Monday, etc), the next columns are the number of stylists available on that weekday, on the different sites.

Having this table, I can use a formula to match a weekday + a site with a number of stylists, from the main sheet. The following formula is for Tuesdays on Site 1:

=VLOOKUP(B$2, Weekdays!$A$2:$D$8,2)

The VLOOKUP function (check out its documentation) takes the value from B2 (Tue, which translates to 2), and matches it against the first column in the Weekdays sheet. When it finds a matching row, it returns the 2nd column - which is the Number of stylists Site 1.

Thus, the formula can be dragged across the entire row for Site 1, and it will adjust automatically.

For the other sites, you need to modify the last parameter of the formula. For Site 2, replace 2 with 3: =VLOOKUP(B$2, Weekdays!$A$2:$D$8,3), and for Site 3, replace with 4: =VLOOKUP(B$2, Weekdays!$A$2:$D$8,4)

I have set up an example spreadsheet to demonstrate this, feel free to copy it.

1

I tried the 'if' formula but couldn't seem to master it!

If you want to use if, you would need to nest several of them one into another:

=if(L1="Mon",5,if(L1="Tues",5,if(L1="Weds",4,if(L1="Thurs",4,if(L1="Fri",3,if(L1="Sat",2,if(L1="Sun",1)))))))

I would use this instead:

=vlookup(L1,{"Mon",5;"Tues",5;"Weds",4;"Thurs",4;"Fri",3;"Sat",2;"Sun",1},2,0)

If your weekdays are actually numbers and not a text, the following formulas woould apply:

=if(L1=2,5,if(L1=3,5,if(L1=4,4,if(L1=5,4,if(L1=6,3,if(L1=7,2,if(L1=1,1)))))))
=vlookup(L1,{2,5;3,5;4,4;5,4;6,3;7,2;1,1},2,0)

  • 1
    I think that using weekday names won't work, in the OP's spreadsheet that row is just formatted as weekdays - it is really a number, 1-7. – Vidar S. Ramdal Sep 18 '15 at 14:21
  • @VidarS.Ramdal - Thanks, I see that's true what you say. I've updated my answer, but I find your answer more practical. – ZygD Sep 19 '15 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.