Possible to Calculate with 3 variables
Hi all,
My excel file is attached. Once I was supported by another forum to build Sheet Event procedure included in file. It is Ok to calculate with 2 variables (Accommodation Type & Board) but can not succeeed to calculate with 3rd variable (Room Type). How can it be reducted to calculate depending 3 variables (Room Type + Accommodation Type + Board)
Thanks in advance for your kindest promts.
just out of curiosity, would the below formula work for you, instead?
Formula:
Please Login or Register to view this content.
modify delimiters etc per your local requirements (e.g. , to ; if nec.)
XLent's solution is very insightful and an elegant example of what can be done with a formula. I would highly recommend adopting that solution.
_____________________________
I worked on your code before I saw the formula solution. Just for comparison I will include it here, with XLent's formula in D9.
There are a lot of problems with this code. It is virtually unreadable. I basically rewrote it.
Your formulas to calculate rates starting in row 50 are wrong. It refers to row 24, but should refer to row 18. This caused the row that referred to row 33 to be all zeroes. I fixed that.
I also removed the blank lines from your rates to make locating the right rate easier.
You also had BED & BREAKFAST repeated twice in the last set of rates, so I changed the last one to HALF BOARD.
I also added formatting to make the rate sheet easier to read. You can easily remove that if you don't like it.
Thanks both of you. I am an amateur trying to automate my job regarding my necessities, so lots of things to be learnt but gonna keep your advices always. Normally it is a very long procedure converting the sheets of hotels prices (tabs) format into the format in "CALC" sheet and very sure that have several mistakes in codes (I also sent another post today because my code can not achieve to select required sheet end of procedure).
XLent's solution seems better than to solve via vba and I tested in many combinations gave the right calculation always. My ranges are dynamic may lead to 500-600 rows for some hotels but my periods (dates) which may vary in each hotel are always fixed in maximum 13 columns (D14:P14) with last date (here 31.10.19) included.
I am sure XLent' solution will work in all probabilities but want to ask if may check start (01.04) & end date (31.10) before calculation. I mean if date is selected before 01.04 or after 31.10 can the formula precheck and give the sum blank at least to be rid of date mistakes in selection. Thanks again
Now I noticed that formula works excellent for this hotel but periods' rows are also dynamic must be started from "D15" but can be extended to "P15" maximum. How can the formula be fixed for such cases?
Please Login or Register to view this content.Dear Xlent I made a small revision now works perfect. By the way forget my last post because working even in extended periods just missed a cell adress while retyping. Thanks a lot againPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks