Forum Discussion

Vid142857's avatar
Vid142857
Copper Contributor
Dec 14, 2022

Help with Excel formula

Hi Folks

 

I'm trying to set up excel formula for an invoice. In column A i have a list of halls we rent (big, small, both) and each have their own rate during the week and a different rate for the weekend. 

 

What I need is…
 
If hall is “Big” and “Date” is weekday then “Rate” is 25
If hall is “Big” and “Date” is weekend then “Rate” is 30
 
If hall is “Small” and “Date” is weekday then “Rate” is 15
If hall is “Small” and “Date” is weekend then “Rate” is 20
 
If hall is “Both” and “Date” is weekday then “Rate” is 40
If hall is “Both"and “Date” is weekend then “Rate” is 75
 
…….in a single formula?!

 

Basically I want the info in column A to dictate the Rate in column E which depends on the Date (weekday/weekend) in column B. 

 

Any help would be greatly appreciated 

Many thanks

Keith

excel
Formulas and Functions

4 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 14, 2022

    Vid142857 

    =INDEX($I$3:$I$8,MATCH(A1&B1,$G$3:$G$8&$H$3:$H$8,0))

    You can set up a reference table and refer to it with INDEX and MATCH. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

     

    • Vid142857's avatar
      Vid142857
      Copper Contributor
      Dec 14, 2022

      OliverScheurich 

       

      Hi Quadruple

       

      Thanks for getting back to me and taking the time to create that formula.

       

      Unfortunately, it's not quite what i need. I didn't explain the issue properly, sorry. We use dates on the invoice, so i'd need something like... if column A is x, y or z AND the date showing in column B falls on a weekend or weekday then the rate in column E is 15 or 20.

       

      The tables below have formula

       =IF(WEEKDAY(B2,2)>5,30,25)......=IF(WEEKDAY(B20,2)>5,20,15).....=IF(WEEKDAY(B36,2)>5,75,40)

       

      and it works ok but it's a different formula for each hall and the hall column is not included in the formula either. 

      So i want to combine these tables/formula into one formula. The Hall column and the Date column would dictate the price in the Rate column. 

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Dec 14, 2022

        Vid142857 

        =INDEX($I$5:$O$7,MATCH(A2,$H$5:$H$7,0),MATCH(WEEKDAY(B2,2),$I$4:$O$4,0))

        You can set up a reference table as shown in the screenshot and apply the above formula.

        An alternative could be a nested IF formula which is in cell F2 and filled down but it's more complicated and more difficult to maintain.

         

        =IF(AND(WEEKDAY(B2,2)>5,A2="Big"),30,IF(AND(WEEKDAY(B2,2)<=5,A2="Big"),25,IF(AND(WEEKDAY(B2,2)>5,A2="Small"),20,IF(AND(WEEKDAY(B2,2)<=5,A2="Small"),15,IF(AND(WEEKDAY(B2,2)>5,A2="Both"),75,IF(AND(WEEKDAY(B2,2)<=5,A2="Both"),40,""))))))
         

Resources