# A Proper Formula to Calculate depending several Criteria

1. ## A Proper Formula to Calculate depending several Criteria

Hi to All,

I am not sure whether a formula is possible to calculate based on the selected criterias on my attached file because there are really lots of parameters affecting the sum of calculation. I tried to build the example file as much identical to my original and with required details as well. Hope someone may achieve to invent a proper formula to solve my crucial problem will be appreciated a lot. However I have no chance to deploy the file on xlsm format must be xlsx.

2. ## Re: A Proper Formula to Calculate depending several Criteria

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

3. ## Re: A Proper Formula to Calculate depending several Criteria

Dear Alan,

Thanks for your attention. I put this forum cross-post link to the top of my query in the other forum but not here, adding below now. Regards

4. ## Re: A Proper Formula to Calculate depending several Criteria

Dear All,

I revised my attached file and wrote a formula in cell AB4 (blue row) by checking sources on internet. However I transferred the required data in tables to the rows on the right, side by side within an order. Unfortunately I am still so far to solve my problem. This formula can display the right base price depending the inputs in C3 and N3 but without calculation. To obtain true calculation the formula in AB3 must evaluate the information at other input boxes, roughly defined below:

1- Date Rows (F3 & H3): How can be possible to take dates rows into attention to affect the calculation especially at periods changes? Eg: Check-in= 28.06.2020 / Check-out= 05.07.2020 (7 nights omitting the last day). So 3 nights must be calculated from Period #: 1 and 4 nights from Period #: 2 to reach the right sum.

2- Children Age Rows (Q3, T3, W3 & Y3): Here the ages are entered as 8 & 11 which should be inserted from low to high always. This family includes 3 adults + 2 children with 8 and 11 ages must be evaluated over discounted price not free. Rows X8 & AB8 are constant and selection here always same for the rest of the periods involves 2 choices “Rate/-% (PP)”. The sum of calculation never change if Children ages are combined under Free Child Ages (O7:T7) columns.

Rate: If Rate is selected the appropriate figure is directly added to calculation. So (27,00 (3 Adt rate in 1st Period) + Children rates * 3 nights) + 40,50 (3 Adt rate in 2nd Period) + Children rates * 4 nights).

“-% (PP): Otherwise “-% (PP)” can be selected (as in attached file) then (27,00 + 5,00 (50,00% from PP Price 10,00) * 2 Chd * 3 nights= 111,00) + (40,50 + 7,50 (50,00% from PP Price 15,00) * 2 Chd * 4 nights= 222,00). Total: 111 + 222= 333,00.

3- Board Row (K3): If Board is entered unlike the Base Board, the selected Board Supplement must be added with respect to the input at N7 (Cumulative/Non-Cumulative; constant row, selection never changes in other periods) but not only for Adults but also for Children.

Cumulative Method: Lets suppose BB is entered to K3; 27,00 (3 Adt rate) / 10,00 (Price/PP)= 2,70 (the multiplier). Then 2,00 (BB Sup) * 2,70 (multiplier)= 5,40 is per day Supplement total for Adults. Children which is 50% discounted (2,00 * 50%= 1 * 2 Chd= 2,00). The calculation for 3 nights 27,00 (Adults Accommodation) + 10,00 (Chd Accommodation “in 2nd Article”) + 5,40 (Adults BB Sup.) + 2,00 (Children BB Sup.) * 3 nights= 133,20. 4 nights; 40,50 + 15,00 + 5,40 + 2= 251,60. Overall: 133,20 + 251,60= 384,80.

Non-Cumulative Method (over same data): Adults: 2,00 (BB Sup) * 3 (Adult # in N3)= 6,00. Children: 2,00 * 50%= 1 * 2 Chd (Q3 & T3)= 2,00. Per day board supplement for adults & children; 6,00 + 2,00= 8,00. Final sum: (27,00 + 10,00 + 8,00 * 3 nights = 135,00) + (40,50+ 15,00 + 8,00 * 4 nights = 254,00) = 389,00

Hope these helps what I request. Thanks indeed for your kindest prompts.

5. ## Re: A Proper Formula to Calculate depending several Criteria

In my opinion the layout of the table in columns AD:AX is an improvement, so well done.
This addresses points 1 and 2.
Four columns are added to the aforementioned table:
Column AG displays the PP cost and is populated using: =IF(ISNUMBER(F9),F9,0)
Column BA displays the cost to the adults and is populated using: =INDEX(AH9:AK9,MATCH(AH\$3,AH\$5:AK\$5,0))*AZ9
Column BB displays the cost to the children (discounted) using: =SUM(IF(AND(Q\$3>=AW9,Q\$3<=AU9),1,0),IF(AND(T\$3>=AT9,T\$3<=AX9),1,0))*AG9*AZ9*0.5
Cell AB3 displays the sum (which matches the manually placed amount) using: =SUM(BA9:BA46,BB9:BB46)
Let us know if you have any questions.

6. ## Re: A Proper Formula to Calculate depending several Criteria

Dear JeteMc,

Thank you very much for your kindest prompts like a life-saver becuse almost loosing my hope to be supported. The calculation is OK except for small mistakes at overnights calculation and even noticed my fault writing of 7 nights at my previous post but must be 8 in total. Check-in is 05/28 so includes 4 overnights (28-29-30-31) till the period change on 06/01 and second term has also 4 overnights (01-02-03-04) 5th is check-out day which is out from calculation.

In my new attachment I deleted your minus formula but added number of overnights formula. The light orange & blue rows are my updates which has been omitted in the previous file. In AG column I also added Board values that is important on selection of rates means user can change Board to be re-calculated depending the new selection; was explained at Article-3 at my previous post as well.

Please also check BE11 & 12 tried to write an experimental formula checking the value in X8 (Rate / -% (PP)) important to define Child rates. I also updated some rows became quite same with my original and put some notes into the file.

I can summarize my remaining problems as:
1- True calculation of overnights and period breaks.
2- Board supplement with respect to the selection on L3 & N7 (Cumulative / Non-Cumulative).
3- Discounted Children calculation regarding ages and no of children may lead to 4 in each accommodation Q3, T3, W3, Y3.

Wishing you health and success

7. ## Re: A Proper Formula to Calculate depending several Criteria

I am not entirely sure that I understand, however I made a couple of changes to the file so that it does show 4 nights in period 1 and 4 nights in period 2.
1. I put the following into cell H4: =H3-1
3. I modified the formula in BC9 to read: =SUM(IF(AND(Q\$3>=AX9,Q\$3<=AV9),1,0),IF(AND(T\$3>=AU9,T\$3<=AY9),1,0))*AH9*BA9*IF(AW9="",0,AW9)/100
Note that #3 is my attempt to interpret the intent of cells BE11:BE12 however I may have missed the point. If so please show us an example illustrating where the formula is yielding incorrect results.
Let us know if you have any questions.

8. ## Re: A Proper Formula to Calculate depending several Criteria

This is my attempt to include board:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

9. ## Re: A Proper Formula to Calculate depending several Criteria

Hi again,

I was just about to send a new post but already saw yours thanks indeed for your concern. I also tried to compose my attached sheet regarding your previous upload but the board calculation in new one seems failed related with the selection at yellow ranges even described below:

1- Period calculation is OK, Adults calculation is totally right.

2- a) Children calculation is OK if data validation in X8 selected as “-% (PP)” but must be changed when validation altered to “Rate”. Rate selection is directly affecting the price as it is (no discount applicable). My experimental formula is about this (some hotels are applying discount but some is offering a special rate).
b) There is also a small problem here to take the number of children into consideration. Because there are 4 boxes Q3, T3, W3 & Y3 to enter the kids ages and if the discounted kids ages are written more than 2 boxes, the calculation is not changing. Suppose entered as 8, 9, 10, 11 so as to multiply PP*50%*4 Chd*Nights.

3- The last problem is Board Supplement selection in L3 should affect the total according to the figures under Supplements (BB Supp, HB Supp, etc.). But here the selection in N7 (N7 always default to determine other N selections) is another criteria to make the right calculation. I tried to fix something at my uploaded file hopefully will clarify the subject more. Below I explain the logic if BB is selected on L3 row with Cumulative & Non-Cumulative calculation methods as well.

Cumulative: 37,00 (per night Adult+chd) / 10,00 (PP rate)=3,7 (multiplier) then 2,00 (BB Supp) * 3,7 (multiplier) = 7,40 (BB supplement total per day). One Night Total:
37,00 (per night Adult+chd) + 7,40 (BB supplement total per day)= 44,40

Non-Cumulative: We do not need multiplier, just amounts of adults and children with discount parameters will manage the sum; 2 (BB Supp) * 3 (Adults #)= 6 (3 Adults Supplement) then 2 (BB Supp) * 50% (Chd Disc Percentage) * 2 (2 kids)= 2 (2 Chd Supp). One Night Total:
37,00 (per night Adult+chd) + 6 (3 Adults Supp) + 2 (2 Chd Supp)= 45,00

*** Free Aged kids are always out from calculation both in accommodation and board

Hope this time is more clear with my new explonations on the sheet. Take care

10. ## Re: A Proper Formula to Calculate depending several Criteria

As to 2a): Looking at the formula in cell BK13 it seems that if X8 is changed to "Rate" then 50.00 will be charged per child, per day, is this correct? If not then please inform us of what the charge would be per child, per day.
As to 2b) the table only shows the age range for children 1 and 2. Should age range be a factor for children 3 and 4? If so then please inform us what the age range is for children 3 and 4?
I'll wait and address point 3 after addressing points 2a and 2b.

11. ## Re: A Proper Formula to Calculate depending several Criteria

Hi, responses:

2a) Yes 50.00 is totally true. If I select rate I have to change the figures to an appropriate one eg: 3.00 but not change in example file.

2b) Age range in table is to show the usual application of Children discounts but sure a factor for the 3rd and 4th children. So all children will be calculated over table criterias by checking the ages of kids. Unfortunately I can not change the table format because it gives the required information regarding the ages of kids (Free or discounted) even for 4 children. In column E9 and below the total number of accommodation (children included) is restricted so it can be 1 adult + 4 Chd or 2 Adults + 3 Chd or 3 Adt + 2 Chd or 4 Adults + 1 Chd but all kids (max 4) should be calculated from Free/Discounted age parameters by age matching. By the way please don' t pay attention to the topic of "1st Child Age" or 2nd Child Age under Free and Discounted Child Ages Rows because it implies the 1st kid must be between .... ages to be Free/Discounted and the 2nd or more kids (3, 4, etc) must be between .... ages to be Free/Discounted.

Regards

12. ## Re: A Proper Formula to Calculate depending several Criteria

See if this will work. I modified the experimental formula in cell BK12 to read:
Formula:
`Please Login or Register  to view this content.`

If it yields the correct results then I suggest pasting it into cell BC9 and copying down.
If that works then we can look at point #3 from post 9.
Let us know if you have any questions.

13. ## Re: A Proper Formula to Calculate depending several Criteria

Dear,

It usually works except for some condition:
1) The ages must be written from high to low but no problem can be. 2) If I enter the ages like 12-11-5-3 the sum must be 60 but here as 40. Calc: Age-12 discounted 5 + Age-11 discounted 5 + Age-5 free 0 (because this age is part of Free Chd for the 1st kid) + Age-3 discounted 5 (part of Disc Chd for the 2nd kid; age is out of 0-02 parameter)= 15 * 4 days= 60. 3) The calculation evaluates the row BK13 but this experimental row has built for 1st Period only maybe we need another column to be applied for other periods because when I dragged the formula it gives same result of 40 in BC23 but it should be 60 over PP rate of 15.

Rgds

14. ## Re: A Proper Formula to Calculate depending several Criteria

In the files attached to previous posts the children's ages are listed low to high. Listing low to high makes it easier to work with because there could be 1, 2, 3 or 4 children, however only the first child would be eligible for the free lodging if their age is less than 6. If you list from high to low then the next to the last child is eligible free lodging if they are under the less than 6 years of age, however you have to add another calculation to determine which is the next to the last one listed, based on cells with values and cells that are blank. Attached is a file showing the experimental formula applied with the ages listed in post #13. Cells BC9 and BC23 both display 60.
Let us know if you have any questions.

15. ## Re: A Proper Formula to Calculate depending several Criteria

I revised the BK column (starting from BK9) and seems working well regarding #3 in my previous post. I also update your formula as
Formula:
`Please Login or Register  to view this content.`
. But #2 is still problem to make true calculation over ages. I attached the revised file.

16. ## Re: A Proper Formula to Calculate depending several Criteria

Here is a set up that would allow the children's ages to be listed high to low.
Let us know if you have any questions.

17. ## Re: A Proper Formula to Calculate depending several Criteria

Sorry to mislead you in my previous post because you are right that the usual implementation is from low to high but I had wrong results when I tried with your suggested formula then entered reverse to test, had better results. Now tested your post# 14 attachment seems to solve most of the problem except for essential 3 conditions explained below:

chd1= 2, chd2= 5, chd3= 11, chd4= 12 SUM: 40 True
chd1= 2, chd2= 7, chd3= 11, chd4= 12 SUM: 60 True
chd1= 3, chd2= 5, chd3= 11, chd4= 12 SUM: 60 True
chd1= 3, chd2= 7, chd3= 11, chd4= 12 SUM: 80 False (must be 60 because chd1 is Free)
chd1= 1, chd2= 2, chd3= 3, chd4= 4 SUM: 0 False (must be 40 because chd1 & chd2 is Free only less than 3 y.o.; starting from 2nd child, ages more than 2 will be charged)
chd1= 3, chd2= 4, chd3= 5, chd6= 6 SUM: 20 False (must be 60 because chd1 is free, rest 3 kids are over than 2)

I just made a small revision in your last formula of post# 14 attachment and the multiplier BK inside now became dynamic dependant to column values to give right results. By the way forget my post# 15 composed before your update.

18. ## Re: A Proper Formula to Calculate depending several Criteria

The following in BC9 and down yields all of the above expected results:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

19. ## Re: A Proper Formula to Calculate depending several Criteria

I believe that the following is more efficient:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

20. ## Re: A Proper Formula to Calculate depending several Criteria

Thank you, calculates well for conditions in post# 17 but sorry for the very rare condition as 4 kids are less than 2 or 1 is less than 7 the other 3 is 2 or less y.o. Result must be 0 for such condition.

21. ## Re: A Proper Formula to Calculate depending several Criteria

Try replacing the formula in BC9 with:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

22. ## Re: A Proper Formula to Calculate depending several Criteria

You are great, works in all conditions . Thank youuu, very appreciated.

Probably turn for #3 but I will request you to check the columns BD:BI seems already solved to calculate Board Supplements. The next step is how to combine the sum in a formula at AB3 based on the selections in data validations. I also attach a clean copy of updated file with your last formulas and delete unrequired items.

23. ## Re: A Proper Formula to Calculate depending several Criteria

Me again. After a careful checking I understood that I was mistaken in board calculation. So Board calculation is still unsolved.

24. ## Re: A Proper Formula to Calculate depending several Criteria

As far as I noticed the problems occur if Cumulative + Rate or Non-Cumulative + Rate are selected. Both Cumulative + -% (PP) & Non-Cumulative + -% (PP) works right.

I defined the methods one more time here for 1st period on 1 night basis (3 adults + 2 discounted chd “2.00 PP BB Supp & 3.00 Disc. Chd Rate at Rate selection”):

1) Cumulative + -% (PP): (37.00 (per night Adults+chd) / 10.00 (PP rate))= 3.7 (multiplier) then 2.00 (BB Supp) * 3.7 (multiplier) = 7.40 (BB supplement total per day). One Night Total:
37.00 (per night Adults+chd) + 7.40 (BB supplement total per day)= 44.40 - This one OK in file

2) Non-Cumulative + -% (PP): Multiplier no needed, adults and children quantity with discount parameters will manage the sum; (2.00 (BB Supp) * 3 (Adults #))= 6.00 (3 Adults Supplement) then (2.00 (BB Supp) * 50% (valid Chd Disc Percentage on table) * 2 (2 kids))= 2.00 (2 Chd BB Supp). One Night Total:
37.00 (per night Adult+chd) + 6.00 (3 Adults Supp) + 2.00 (2 Chd BB Supp)= 45,00 - This one OK in file

3) Cumulative + Rate: 27.00 (per night Adults) then (3.00 (Child disc. Rate) * 2 (Chd #))=6.00 (per night Children) then (2.00 (BB Supp) * 3.7 (multiplier))= 7.40 (BB supplement total per day). One Night Total:
27.00 (per night Adults) + 6.00 (per night Children) + 7.40 (BB supplement total per day)= 40.40 (One Night total) This one FAILED in file

4) Non-Cumulative + Rate: 27.00 (per night Adults) then (3.00 (Child disc. Rate) * 2 (Chd #))=6.00 (per night Children) then (2.00 (BB Supp) * 3 (Adults #))= 6.00 (3 Adults BB Supplement) then (2.00 (BB Supp) * 50% (valid Chd Disc Percentage on table) * 2 (2 kids))= 2.00 (2 Chd BB Supp). One Night Total:
27.00 (per night Adults) + 6.00 (per night Children) + 6.00 (3 Adults BB Supplement) + 2.00 (2 Chd BB Supp)= 41.00 This one FAILED in file

Hope these helps. Regards

25. ## Re: A Proper Formula to Calculate depending several Criteria

3) Cumulative + Rate: 27.00 (per night Adults) then (3.00 (Child disc. Rate) * 2 (Chd #))=6.00 (per night Children) then (2.00 (BB Supp) * 3.7 (multiplier))= 7.40 (BB supplement total per day). One Night Total:
27.00 (per night Adults) + 6.00 (per night Children) + 7.40 (BB supplement total per day)= 40.40 (One Night total) This one FAILED in file
I have attempted to set the file to model scenario 3). In looking back at post #4 the multiplier is defined as the per night adults divided by the PP rate. If that is the case then it would seem the multiplier for scenario 3) should be 2.7 Once I get confirmation about scenario 3), I will attempt to work on scenario 4)
Let us know if you have any questions.

26. ## Re: A Proper Formula to Calculate depending several Criteria

Dear,
Implementation for Cumulative method of bording, dividing adults to PP rate is true to find the multiplier for 3 Adults but if the case is 3 Adults + 2 Disc Chd it becames 3.7 because 2.7 (adults) + 0.5 (1 kid) + 0.5 (1 kid)= 3.7 becomes the current multiplier. So total of boarding occurs 7.40 (2.00 * 3.7) .
If discounted child is increased to 3 it will be 4.2 (+0.5).

Non-Cumulative: 3 adults * 2 (BB Supp)= 6.00 (3 adults supp) & 2.00 (Bb Supp) * 50% (this percentage is variable if Chd Disc is selected as “-%” but constant if selected as Rate in Chd Disc) * 2 = 2.00 . Total: 6.00 + 2.00= 8.00 (adults+chd).

Regarding the selections of 3 adt + 2 disc chd (rate base of 3.00 for accommodation & 2.00 BB supp)
Cumulative: 27.00 + 6.00 + 7.40= 40.40
Non-Cumulative: 27 + 6.00 + 6.00 (adults BB supp) + 2.00 (chd BB supp)= 41.00

27. ## Re: A Proper Formula to Calculate depending several Criteria

Please modify the formula in column BK (file attached to post #25) so that it reads:
Formula:
`Please Login or Register  to view this content.`

I want to make sure that the non-cumulative results are yielding the correct results before attempting to resolve the cumulative results.
Let us know if that yields the correct results and if you have any questions.

28. ## Re: A Proper Formula to Calculate depending several Criteria

I am not sure whether I was misunderstood from your information in post# 27. After applying the formula it gives true result with Cumulative method but fails with Non-Cumulative method opposite to your statement.

29. ## Re: A Proper Formula to Calculate depending several Criteria

You are correct. I got the terms backwards.

30. ## Re: A Proper Formula to Calculate depending several Criteria

Hopefully the file is rendering the correct amount for non-cumulative at this point.
The formula in AB3 is: =SUM(BB9:BC46,IF(N7="cumulative",BL9:BO46,BP9:BS46))
The formula that populates BP9:BS46 is: =IF(OR(\$AM\$3<>BP\$7,\$BA9=0),"",SUM(\$N\$3*AM9,\$BE\$5*\$AW9))
Let us know if you have any questions.

31. ## Re: A Proper Formula to Calculate depending several Criteria

Hi,
1) Non-Cumulative & Rate Sum: 45.00 but must be 41.00:
((10*2.7= 27) + (3.00 * 2= 6.00)) + ((2.00 * 3= 6.00) + (2.00 * 50% * 2= 2.00))= 41.00

2) Cumulative+Rate Sum: 40.40 True

3) Non-Cumulative & -50% Sum: 143.00 but must be 45.00
(10.00 * 3.7= 37.00) + ((2.00 * 3= 6.00) + (2.00 * 50% * 2= 2.00))= 45.00

4) Cumulative & -50% Sum: 44.40 True

I’ ve noticed 2 points as #1 false sum must be #3 sum and you have not applied BK column formula update suggested last night.

Regards

32. ## Re: A Proper Formula to Calculate depending several Criteria

As to the formula in column BK, I felt that it would be more efficient to put the following into cell BE5 and use that result in the calculation for column BK:
Formula:
`Please Login or Register  to view this content.`

I have also changed the formula in BP9 to read:
Formula:
`Please Login or Register  to view this content.`

I had thought that the *0.5 part was used only in -%PP instances, so I am confused as how it applies to scenario 1)
As to scenario 3), it appears that (10.00*3.7=37.00) is the adult charge. I am confused because I thought that cell AK9 (27.00) was the charge for a standard land view with 3 adults.
The attached file is set up for scenario 1) and shows the expected result for scenario 2) but not 3) nor 4)
Perhaps another contributor will be able to understand. If not I'll try to get back to this later.

33. ## Re: A Proper Formula to Calculate depending several Criteria

Hello again,

I worked on your formulas till 5 a.m. yesterday but enlightened me a lot to attempt a few revisions today. However I understood that I owed you an apologize for information shortage of Children discounts. The figures are how much to be reducted from PP rate if “-% (PP)” is selected and varies to 55, 60, etc. So 60 means PP*0,4 but corrected them all, adding 1 to the front of required formulas. I also made some revisions to delete the unrequired columns can be seen in attachment. I almost tested for different amounts and percentages seems to give right sum at all choices.

Revisions (row adresses changed because of columns deletion):
BG9
Formula:
`Please Login or Register  to view this content.`

BI9
Formula:
`Please Login or Register  to view this content.`

H4 has moved to BA5

Well 0.5 is valid for case “Cumulative & Rate” condition only because children accommodation rate can not provide a discount multiplier so as to admit as 50% from board supplement. 3rd adult charge is 2.7 here but can be applied as 2.75, 2.8 a variable, but becomes 3.7 together with kids (2.7 + 0.5 (1st kid) + 0.5 (2nd kid)=3.7).

One more thanks for your great support and please post a confirmation to assign this thread as solved.

Best Regards.

34. ## Re: A Proper Formula to Calculate depending several Criteria

You're Welcome and thank you for the feedback. The worksheet looks good and I am glad that it is working the way you want.
If I may make one comment it is that the formula in BI9 and down has some extra AND's in the logical tests of the IF functions. substituting the following should yields the same result as before and should use fewer resources to accomplish that goal:
Formula:
`Please Login or Register  to view this content.`

I hope that you have a blessed day.

35. asdffasdff

36. ## Re: A Proper Formula to Calculate depending several Criteria

Yes you are right, learning each post . Take care

There are currently 1 users browsing this thread. (0 members and 1 guests)