Home |
Search |
Today's Posts |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "Joel" wrote: 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)) "Narnimar" wrote: 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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
CONDITIONAL FORMULA | New Users to Excel | |||
Conditional Formula to indicate Formula in cell | New Users to Excel | |||
conditional formula | Excel Discussion (Misc queries) | |||
Conditional Sum Formula? | Excel Discussion (Misc queries) | |||
A little help with a conditional formula, please. | Excel Worksheet Functions |