Question description:
This user has given permission to use the problem statement for this
blog.
I have 5 or so variables that need to be checked in order to determine what price/unit i should use, there are 6 different price/unit. at the moment i am using really long IF(AND(),"","") statements and was wondering if there is a more efficient function i can use to determine this?
Solved by E. E. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
22/04/2018 - 12:49
yea its more or a one off spreadsheet for data management/booking
Excelchat Expert
22/04/2018 - 12:49
Welcome to got it pro
Excelchat Expert
22/04/2018 - 12:49
I will help you today.
Excelchat Expert
22/04/2018 - 12:50
please tell me details.
User
22/04/2018 - 12:50
ok so is there a more efficient formulas to use that accounts for multiple variables besides using if and AND statements?
User
22/04/2018 - 12:51
i am trying to develop a sheet that determines a rate per night based on a number of client variables
User
22/04/2018 - 12:51
where the rate per night varies depending of the date of the booking
Excelchat Expert
22/04/2018 - 12:52
I need to look at the data.
User
22/04/2018 - 12:52
there are 3 different rates depending on time of year
Excelchat Expert
22/04/2018 - 12:52
Can you do that?
Excelchat Expert
22/04/2018 - 12:53
Please list the variables in the preview window.
User
22/04/2018 - 12:55
does that make sense?
Excelchat Expert
22/04/2018 - 12:55
not really.
Excelchat Expert
22/04/2018 - 12:55
This gives me an idea of where do you need the formula.
Excelchat Expert
22/04/2018 - 12:56
But I need to know the determinants for nightly rates.
User
22/04/2018 - 12:56
so trying to use variable such as: age, member status, season, number of nights/ weekend?, in order to determine the nightly rate
User
22/04/2018 - 12:57
at teh moment i am considering using If statements combined with And statements for the logic part to determine this
User
22/04/2018 - 12:57
but was wondering if there is a more efficient function
Excelchat Expert
22/04/2018 - 12:57
There is.
User
22/04/2018 - 12:57
=]
Excelchat Expert
22/04/2018 - 12:57
We can use index/match
User
22/04/2018 - 12:58
ok how would you use those functions? i havent used them before..
Excelchat Expert
22/04/2018 - 12:59
I'm asking for the determinants to show you how to use it.
Excelchat Expert
22/04/2018 - 01:00
We need to create a table.
Excelchat Expert
22/04/2018 - 01:00
Then using that table, we can create the formula.
User
22/04/2018 - 01:01
does what i just input into the preview help?
Excelchat Expert
22/04/2018 - 01:01
it doesn't.
Excelchat Expert
22/04/2018 - 01:01
I need the complete list.
Excelchat Expert
22/04/2018 - 01:01
Please look at column T.
Excelchat Expert
22/04/2018 - 01:02
Please fill the table with the variables.
User
22/04/2018 - 01:02
ahk, so i don't havent that information yet as it will be a "living sheet" with the data inut as it comes in..?
Excelchat Expert
22/04/2018 - 01:03
Then the only thing I can show you is the general syntax of index/match
User
22/04/2018 - 01:04
ok if you could and then i'll try figuring out how it works.
User
22/04/2018 - 01:04
thankyou
Excelchat Expert
22/04/2018 - 01:05
Give me a few minutes.
Excelchat Expert
22/04/2018 - 01:12
=ArrayFormula(index($D$15:$D$18,match(1,($A$15:$A$18=P3)*($B$15:$B$18=Q3),0)))
Excelchat Expert
22/04/2018 - 01:12
Please see the result now.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
