Dear Malaik,

Instead of Conditional Formatting you can do it by HLOOKUP formula as below:

I am using same figures of days & Per Day amount from your following example.

In exel vertically define a table as below:

Column
A B C
Days 0 6 11
Amount 80 160 320

In your excel sheet horizontallay u have days as below and the HLOOKUP
formula placing the figures below and giving the exact total of 2160 as
arrived by you.

days Amt
1 80
2 80
3 80
4 80
5 80
6 160
7 160
8 160
9 160
10 160
11 320
12 320
13 320
Total 2160

This is a most powerful function in excel. If you still can't understand, go
to HLOOKUP help in excel where it explain you in detail as how to use this
function step by step.

Regards,

Sambhaji


"Malik Nadeem" wrote:

A B C D
E
-----------------------------------------------------------------------------
05-nov =now()-a1 5 1
(result i need here)
----------------------------------------------------------------------------
05-nov 65 5 1
258000
----------------------------------------------------------------------------

in a column E will work like this

* condition # 1
if column B1 is less then or equal to 5 then cell E1 showing "FREE"

* 2nd condition in a same cell E1
if column B1 is grater then 5 then (B1-C1) i.e. equal to 3
now this 3 result multiply with D1 and multiply with $80 per day mean
2days=160, 3 days=240, 4 days=320 & 5 days=400 stop here
in above condition 3 days mean = $240
now (3*80*D1)or(240*D1) till the value is less or equal to 5
example
A B C
05-jan 08 5 = free till 10/jan (rest days 3)
=D1*(if value 1=80, 2=160, 3=240, 4.....5=400)
A=unit rcvd date
B=(=now()-A1)
C=free days
D= total units

* 3rd condition
if value grater then 5 and less then or equal to 10 it should be work like
this
(1st 5 days calulating like condition # 2 itself
but on 6 day it will charge 160 per day)

example
1st 5 days 80 per day
next 5 days 160 per days
after this per day charges will be 320

result
charging of 13 days
0-5 days = 400 (80 p/day)
6-10 days = 800 (160 p day)
11-.... days = 320 per day................

total 13 days result is (400+800+960)=2160

i realy thankful that person who can help me out to make a such kind of
conditional formula

regards
Malik Nadeem