LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 132
Default CONDITIONAL FORMULA

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CONDITIONAL FORMULA SSJ New Users to Excel 7 November 15th 06 06:03 PM
Conditional Formula to indicate Formula in cell SteveW New Users to Excel 9 August 2nd 06 01:12 AM
conditional formula Beth104 Excel Discussion (Misc queries) 2 March 8th 06 09:51 AM
Conditional Sum Formula? Boulder257 Excel Discussion (Misc queries) 1 January 25th 06 05:06 PM
A little help with a conditional formula, please. Altstatten Excel Worksheet Functions 4 January 2nd 06 08:43 PM


All times are GMT +1. The time now is 02:29 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"