CONDITIONAL FORMULA

[Narnimar]

I have to calculate the Total Rental payment amount which is the sum of two
separate Price Lists both have price conditions as per the sizes in Column A
into the No. of chargable Days in D.
A B C D
E
SIZE(TEXT) STARTS ON NOS CHARGABLE DAYS AMOUNT
1 X 20ft 09/11/06 1 98 ?
2 X 20ft 10/11/06 2 97 ?
3 X 20ft 10/11/06 3 97 ?
4 X 20ft 10/11/06 4 97 ?
5 X 20ft 10/11/06 5 97 ?
1 X 40ft 15/11/06 1 92 ?
2 X 40ft 16/11/06 2 91 ?
3 X 40ft 16/11/06 3 91 ?
4 X 40ft 16/11/06 4 91 ?
5 X 40ft 16/11/06 5 91 ?
In the above A is actually a text. B is dd/mm/yy formatted Column,
C=MID(A2:A10;1;1) D is No. of Days Chargable =Today()-Start Date.
Now 2 Price lists are to be refered and the correct prices are to be
selected from both lists then and Sum Amount should show in Column E
according to the no. of Chragable days In Column D. Price Lists are Below.
LIST1 -
DAYS 20ft 40ft
8 to 17 19.00 38.00
18 to 32 24.00 43.00
33 to 47 30.00 48.00

LIST2 -
8 to 14 35.00 70.00
15 to 180 70.00 140.00

Anybody can help me in this complex calculation please?

 

[Joel]

To make thing easier I would create sub totals for list1 and list 2 and total
the two together. the fomula is simplier. Also add a column to extractt 20
or 40.
if(mid,a10,3,2) in column E

Assume this goes in row 10
list1 total
=if(strcomp(E10 = "20") = 0,if(c10 < 17,19*D10,if(c10 < 32, 24*d10,if(c10 <
47,30*D10))),if(c10 < 17,38*D10,if(c10 < 32, 43*d10,if(c10 < 47,48*D10)))


list2
=if(strcomp(E10 = "20") = 0,if(c10 < 14,35*d10,if(c10 < 180, 70*c10)),if(c10
< 14,70*d10,if(c10 < 180, 140*c10))

 

[PaulW]

Maybe not the best way of doing this, but how I'd do it.

Start off with =FIND("40",A2) which will tell you if its 20ft (error) or
40ft (not error) this will fall over if you rent out 40 of them.

I'd probably have the price lists as lists so, for list 1 list from 1 to 47
(no days over 47?) and have the two prices next to that

So, =VLOOKUP(D2,X1:X50,IF(ISERROR(FIND("40",A2)),2,3) will give you the
first price.

Maybe put an if statement for what you want it to be if the number isn't
there? (1 day? 50 days?) probably with an ISERROR, or use > or <
Then do similar for the second price list and add them together.

Sorry I can't go through the entire thing, but i'm off home in a couple of
minutes. But hopefully this should get you there.

 

[Narnimar]

Hi,I am just trying start as per your guidance. It says "you have entered too
many arguments and highlights the number 2 in the formula if(mid,a10,3,2).
What is alternative? Thank you.