Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to write UDF to calculate value based on 8 conditions
Calculate net cost per person based on the following conditions:
Scenario: I am creating a template for users to calculate tour package prices per person. Each package consists of various services and a service can be: a) either a price per person (like an admission fee or a lunch in a restaurant) or b) a "per unit fee" (like a private boat rental, coach rental, a guide fee) a) per person fee can vary depending on the number of participants, for example a restaurant can charge 10 USD for 1 - 4 persons, 8 USD for 5 - 9 persons, 6 USD for 10 - 40 persons b) a per unit fee would be for example a vehicle rental fee costs 20 USD for maximum of 2 persons, 22 USD for maximum 6 persons, 35 USD for max 14 persons, 60 USD for max 40 persons To accommodate the various net cost options, I am using 16 columns to input our net cost: Column G = Cost I per person (formatted as accounting) Column H = Max. persons for Cost I ' max number of persons for who price in Column I is applicable Column I = Cost II per person Column J = Max persons for Cost II ' max number of persons for who price in Column II is applicable .......... Column P = Cost I per unit Column Q = Max persons Unit I = pax number of persons for who the unit price applies Column R = Cost II per unit Column S = Max persons Unit II .... In column Z, AA, AB... etc we enter the number of persons in a group (for example we need a per person price based on 10 participants, 15 participants, 20 participants etc) Now! Column Z, AA, AB shall calculate the tour price PER PERSON per row and I need to evaluate 8 columns and calculate the price from another 8 columns, depending on the result of the IF statement. Nested If Functions allow a maximum of 7 conditions and I assume some VBA function would do the trick...but even after reading up on VBA for days I have no clue how to define the function. The statement would loook like this : IF(Z$1<=$H2,$G2 'if number of participants (Z1) is same or less than max number of persons in (H2), display price from bracket I (G2) IF(Z$1<=$J2,$I2 'if number of participants (Z1) is same or less than max number of persons in (J2), display price from bracket I (I2) IF(Z$1<=$L2,$K2 'if number of participants (Z1) is same or less than max number of persons in (L2), display price from bracket I (K2) IF(Z$1<=$N2,$M2 'if number of participants (Z1) is same or less than max number of persons in (N2), display price from bracket I (M2) IF(Z$1<=$Q2,$P2/Z$1 'if number of participants (Z1) is same or less than max number of persons in (Q2), divide the unit price from (P2) by number of participants (Z1) IF(Z$1<=$S2,$R2/Z$1 'if number of participants (Z1) is same or less than max number of persons in (S2), divide the unit price from (R2) by number of participants (Z1) IF(Z$1<=$U2,$T2/Z$1 'if number of participants (Z1) is same or less than max number of persons in (U2), divide the unit price from (T2) by number of participants (Z1) IF(Z$1<=$W2,$V2/Z$1, if number of participants (Z1) is same or less than max number of persons in (W2), divide the unit price from (V2) by number of participants (Z1) ROUNDUP(Z$1/$Q2,0)*$P2/Z$1 ' else divide the number of participants (Z1) by the max number of persons in (Q2) and round the result up to next integer, then multiple the result with the unit price (P2) and divide the result by the number of participants (if for example, a boat can seat a maximum of 6 persons and we have 10 participants, we need 2 boats, thus multiply the cost for 1 boat x2 and divide the result by the number of participants to get the price per person. I hope, above makes sense and I appreciate every help I can get! Thanks! Niki |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to write UDF to calculate value based on 8 conditions
It appears you need 3 sets of Select Case statements, all based on Z1,
but having different tests and different consequences. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to write UDF to calculate value based on 8 conditi
Hi Merjet
I am aware that a select case statement would fulfill my requirements, my problem though is that I do not know how to write the function :-( "merjet" wrote: It appears you need 3 sets of Select Case statements, all based on Z1, but having different tests and different consequences. Hth, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate accumulative total, based on conditions of two different | Excel Discussion (Misc queries) | |||
2 conditions needed to check and count of items based on that | Excel Worksheet Functions | |||
students grades, formulae to calculate what grade is needed based. | Excel Worksheet Functions | |||
How do I calculate sum based on 3 conditions? | Excel Worksheet Functions | |||
How to write a VBA code to select certain rows based on certain conditions | Excel Programming |