Spreadsheet: 672094d1586731183-a-proper-formula-to-calculate-depending-several-criteria-eg6-zrs-3
Sheet: Sayfa1

 | YELLOW ROWS are with Data Validation |  |  |  |  |  |  |  |  | Board Selection. Please check from AG9 and below after selection in L3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | No of Discounted Chd is important can be 4 maximum. If top 4 boxes are all entered from discounted Chd ages then 5 (disc. Rate)*4 (kids no)= 20,00 (explained well in BK12)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I entered some new formulas into the blue ranges to calculate Board Supplements
 | Room Type | Standard Land View |  | From | 2020-05-28 00:00:00 | To | 2020-06-05 00:00:00 | Nights |  | Board | BB | Adult # | 3 |  | Chd-1 (y.o) | 8 |  | Chd-2 (y.o) | 11 |  | Chd-3 (y.o) |  | Chd-4 (y.o) |  |  | SUM |  | Now the problem is how to combine all parameters in AB3 considering |  |  |  |  | Price/PP |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | regarding the selection at row L3 & N7 seems working well.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | the selections in the boxes on the top and at yellow ranges of price table
 |  |  |  |  |  |  |  |  |  | From |  | To |  |  |  |  |  |  |  |  |  |  |  | Answer to #3: Please change selection here to "Rate" and check BK12 (former BE11) |  |  |  |  |  |  |  | Board | Price/PP | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  | 2nd Child Age |  | 1st Child Age |  |  | 2nd Child Age
 | Rates are on | [NAME] |  | Base Board | BO |  |  | Period #: |  | 2020-05-01 00:00:00 | - | 2020-05-31 00:00:00
 | Room Type | Min & Max Accommodations |  |  | Price/PP | [NAME] (Room Rates) |  |  |  | PP Meal Supplements |  |  | Cumulative | Free Child Ages |  |  |  |  |  | Discounted Child Ages |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Disc. |  | Base Rate | Board | Board | Adult | Disc. Chd.
 |  | Min Adt | [NAME] | [NAME] |  | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  |  | 2nd Child Age |  |  | 1st Child Age |  |  | -% (PP) | 2nd Child Age |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | days | Adults | Children | Price/PP | Per Day | Total PD | Total | Board PD | Board PD
 | Standard Land View | 1 | 4 | 5 | 10 | 15 | 20 | 27 | 32 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | It is OK but X8 data validation selection must be taken into attention as in my experimental formula
 | Std. Pool View | 1 | 3 | 3 | 15 | 22.5 | 30 | 40.5 |  | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2nd problem is no of children in Q3, T3, W3, Y3 rows. If more than 2 discounted children are entered the sum is not changing
 | Std. Side Sea View | 1 | 3 | 4 | 20 | 30 | 40 | 54 |  | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Sea View | 1 | 4 | 4 | 25 | 50 | 50 | 67.5 | 80 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | My experimental formula here affects the calculation
 |  |  |  |  |  | 0 | 0 | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | based on the selection on AW5 (dynamic use as [NAME] or Discount Percentage from X8)
 |  |  |  |  |  | 0 | 0 | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | X8 & AB8 are always with same text but still have problem how to calculate
 |  |  |  |  |  | 0 | 0 | 0 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | if discounted children amount is more than 2 (up to 4)
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 | FEATURES
 | Rates are on | [NAME] |  | Base Board | BO |  |  | Period #: |  | 2020-06-01 00:00:00 | - | 2020-06-20 00:00:00
 | Room Type | Min & Max Accommodations |  |  | Price/PP | [NAME] (Room Rates) |  |  |  | PP Meal Supplements |  |  |  | Free Child Ages |  |  |  |  |  | Discounted Child Ages
 |  | Min Adt | [NAME] | [NAME] |  | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  |  | 2nd Child Age |  |  | 1st Child Age |  |  |  | 2nd Child Age
 | Standard Land View | 1 | 4 | 5 | 15 | 22.5 | 30 | 40.5 | 48 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Pool View | 1 | 3 | 3 | 20 | 30 | 40 | 54 | 0 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Side Sea View | 1 | 3 | 4 | 25 | 37.5 | 50 | 67.5 | 0 | 2 | 5 | 10 | 15 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 | FEATURES
 | Rates are on | [NAME] |  | Base Board | BO |  |  | Period #: |  | 2020-06-21 00:00:00 | - | 2020-07-07 00:00:00
 | Room Type | Min & Max Accommodations |  |  | Price/PP | [NAME] (Room Rates) |  |  |  | PP Meal Supplements |  |  |  | Free Child Ages |  |  |  |  |  | Discounted Child Ages
 |  | Min Adt | [NAME] | [NAME] |  | 1 Adt | 2 Adt | 3 Adt | 4 Adt | BB Supp | HB [NAME] | FB Supp | AI Supp | 1st Child Age |  |  | 2nd Child Age |  |  | 1st Child Age |  |  |  | 2nd Child Age
 | Standard Land View | 1 | 4 | 5 | 20 | 30 | 40 | 54 | 64 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Pool View | 1 | 3 | 3 | 25 | 37.5 | 50 | 67.5 | 0 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Side Sea View | 1 | 3 | 4 | 30 | 45 | 60 | 81 | 0 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Sea View | 1 | 4 | 4 | 35 | 70 | 70 | 94.5 | 112 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 | Std. Garden View | 1 | 3 | 3 | 25 | 37.5 | 50 | 67.5 | 0 | 3 | 6 | 12 | 20 | 0 |  | 6 | 0 |  | 2 | 7 |  | 12 | 50 | 3 |  | 12 | 50
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0
 | FEATURES