A Proper Formula to Calculate depending several Criteria

candeniz

New Member
Joined
Jan 16, 2015
Messages
18
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.
 
Last edited by a moderator:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

candeniz

New Member
Joined
Jan 16, 2015
Messages
18
Hi. I noticed that made a mistake at date selection. The valid values: F3= 05/29/2020 & H3: 06/05/2020 (May/June).
 

Forum statistics

Threads
1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top