A Proper Formula to Calculate depending several Criteria

Administrative Note:

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.)

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

Crosspost: https://chandoo.org/forum/threads/a-...riteria.43987/

Crosspost: https://www.mrexcel.com/board/thread...teria.1130142/

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.

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 AZ displays the days stayed and is populated using: =IF(OR(AF9<>C$3,AD9=""),0,MAX(0,MIN(AE9,H$3)-MAX(AD9,F$3)))
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.

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
2. I modified the formula in BA9 so that it reads: =IF(OR(AF9<>C$3,AD9=""),0,MAX(0,MIN(AE9,H$4)-MAX(AD9,F$3)+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.

This is my attempt to include board:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

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.

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.

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

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.

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.

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.

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.

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.

Try replacing the formula in BC9 with:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

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.

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.

You are correct. I got the terms backwards.

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.

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.

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.

To mark the thread as 'Solved' you, as the originator of the thread, may use the thread tools menu above your first post.
I hope that you have a blessed day.

asdffasdff