Crosspost: A Proper Formula to Calculate depending several Criteria
and A Proper Formula to Calculate depending several Criteria
Linked File: EG7
Dear All,
I wrote a formula in cell AB4 (blue row) of the linked file by checking sources on internet. However I transferred the required data in tables via formulas 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 (all data is from my linked excel file):
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 applicable 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 to offer me a proper formula in row AB3. Thanks indeed for your kindest prompts.
and A Proper Formula to Calculate depending several Criteria
Linked File: EG7
Dear All,
I wrote a formula in cell AB4 (blue row) of the linked file by checking sources on internet. However I transferred the required data in tables via formulas 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 (all data is from my linked excel file):
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 applicable 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 to offer me a proper formula in row AB3. Thanks indeed for your kindest prompts.
Last edited by a moderator:
