Help needed to write UDF to calculate value based on 8 conditions

[Guest]

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

 

[merjet]

It appears you need 3 sets of Select Case statements, all based on Z1,
but having different tests and different consequences.

Hth,
Merjet

 

[Guest]

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 :-(