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

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

One way:

E1: =IF(B1<=C1,"FREE",(B1+ MAX(B1-C1,0)+2*MAX(B1-2*C1,0))*D1*80)

However, I suspect you really need to use

B1: =TODAY()-A1

rather than

B1: =NOW()-A1

since NOW() includes the time, so

06-JAN 00:00:01 - 01-JAN

will return 5.000011574


In article ,
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

Dear JE McGimpsey

below formula is not working properly see below examples

A= 08-JAN-2009
B= 6
C=5
D=1
after using your formula in E1 column result showing 560 instead of 80
====
example
====
A= 01-JAN-2009
B= 13
C=5
D=1
after using your formula in E1 column result showing 2160 instead of 880
=====
another example
=====
A= 08-JAN-2009
B= 6
C=5
D=1
her user formula working correctly showing result 7600 which i required
kindly advise same formula how work with above two examples
there are 3 slaps
one slap is 1-5 days
2nd slap is 6-10 days
3rd slap is 11-to 999

can i sent you the excel file ? if yes kindly advise your email address.

thanks in advnace for your great support and help

regards
Malik Nadeem







"JE McGimpsey" wrote:

One way:

E1: =IF(B1<=C1,"FREE",(B1+ MAX(B1-C1,0)+2*MAX(B1-2*C1,0))*D1*80)

However, I suspect you really need to use

B1: =TODAY()-A1

rather than

B1: =NOW()-A1

since NOW() includes the time, so

06-JAN 00:00:01 - 01-JAN

will return 5.000011574


In article ,
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

I'm obviously not understanding your specifications...

In your first example below, you say that 13 days should = 2160:

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

In your latest example, you say it should be 880.

B= 13
C=5
D=1
after using your formula in E1 column result showing 2160 instead of 880

Which is it and why?



In article ,
Malik Nadeem wrote:

Dear JE McGimpsey

below formula is not working properly see below examples

A= 08-JAN-2009
B= 6
C=5
D=1
after using your formula in E1 column result showing 560 instead of 80
====
example
====
A= 01-JAN-2009
B= 13
C=5
D=1
after using your formula in E1 column result showing 2160 instead of 880
=====
another example
=====
A= 08-JAN-2009
B= 6
C=5
D=1
her user formula working correctly showing result 7600 which i required
kindly advise same formula how work with above two examples
there are 3 slaps
one slap is 1-5 days
2nd slap is 6-10 days
3rd slap is 11-to 999

can i sent you the excel file ? if yes kindly advise your email address.

thanks in advnace for your great support and help

regards
Malik Nadeem







"JE McGimpsey" wrote:

One way:

E1: =IF(B1<=C1,"FREE",(B1+ MAX(B1-C1,0)+2*MAX(B1-2*C1,0))*D1*80)

However, I suspect you really need to use

B1: =TODAY()-A1

rather than

B1: =NOW()-A1

since NOW() includes the time, so

06-JAN 00:00:01 - 01-JAN

will return 5.000011574


In article ,
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

no sir,

formula should work with hide value for example

formula will work with

B = 13 (total days)
C = 5 (free days) amount will not charges giving the relexation to client
D = 1 (number of units)
formula should work like this
13-5=8 (on 8 formula will work on slap basis)
1-5 days $80
6-10 days $160
11-999 days $320

result

A= 08-JAN-2009 (unit returning date)
B= 6 (till todate)
C=5 (free days deduct from b1 that value is 6. balance days is 1 and it
will multiply with $80 beacuse 1 covered under 1-5 lap/slap
D=1 (total unit)
after using formula i need in E1 column result showing 80 instead of 560
---------
2nd condition
---------
when we deduct c1 from b1 beacuse c1 is free days which we gave to customer
without any charge
*hiden result is between 6-10 days 2nd lap will apply i.e. $160
se example
A= 01-JAN-2009
B= 13
C= 5 (c1-b1) = 8 we will charges amount from customer of 8 days like this
1st 5 days $400(80+80+80+80+80) after that 3 days (160+160+160) =880 of 8
days after free time mean C1
--------------
A= 15-dec-2008
B= 30
C= 5 (c1-b1) = 25 we will charges amount from customer of 25 days like this
1st 5 days $400(80+80+80+80+80) after that 5 days (160+160+160+160+160)
after that (320+320+320.......15 times) =7600

hope above is clear.

regards
Malik Nadeem


"JE McGimpsey" wrote:

I'm obviously not understanding your specifications...

In your first example below, you say that 13 days should = 2160:

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

In your latest example, you say it should be 880.

B= 13
C=5
D=1
after using your formula in E1 column result showing 2160 instead of 880

Which is it and why?



In article ,
Malik Nadeem wrote:

Dear JE McGimpsey

below formula is not working properly see below examples

A= 08-JAN-2009
B= 6
C=5
D=1
after using your formula in E1 column result showing 560 instead of 80
====
example
====
A= 01-JAN-2009
B= 13
C=5
D=1
after using your formula in E1 column result showing 2160 instead of 880
=====
another example
=====
A= 08-JAN-2009
B= 6
C=5
D=1
her user formula working correctly showing result 7600 which i required
kindly advise same formula how work with above two examples
there are 3 slaps
one slap is 1-5 days
2nd slap is 6-10 days
3rd slap is 11-to 999

can i sent you the excel file ? if yes kindly advise your email address.

thanks in advnace for your great support and help

regards
Malik Nadeem







"JE McGimpsey" wrote:

One way:

E1: =IF(B1<=C1,"FREE",(B1+ MAX(B1-C1,0)+2*MAX(B1-2*C1,0))*D1*80)

However, I suspect you really need to use

B1: =TODAY()-A1

rather than

B1: =NOW()-A1

since NOW() includes the time, so

06-JAN 00:00:01 - 01-JAN

will return 5.000011574


In article ,
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

no sir,

formula should work with hide value for example

formula will work with

B = 13 (total days)
C = 5 (free days) amount will not charges giving the relexation to client
D = 1 (number of units)
formula should work like this
13-5=8 (on 8 formula will work on slap basis)
1-5 days $80
6-10 days $160
11-999 days $320

result

A= 08-JAN-2009 (unit returning date)
B= 6 (till todate)
C=5 (free days deduct from b1 that value is 6. balance days is 1 and it
will multiply with $80 beacuse 1 covered under 1-5 lap/slap
D=1 (total unit)
after using formula i need in E1 column result showing 80 instead of 560
---------
2nd condition
---------
when we deduct c1 from b1 beacuse c1 is free days which we gave to customer
without any charge
*hiden result is between 6-10 days 2nd lap will apply i.e. $160
se example
A= 01-JAN-2009
B= 13
C= 5 (c1-b1) = 8 we will charges amount from customer of 8 days like this
1st 5 days $400(80+80+80+80+80) after that 3 days (160+160+160) =880 of 8
days after free time mean C1
--------------
A= 15-dec-2008
B= 30
C= 5 (c1-b1) = 25 we will charges amount from customer of 25 days like this
1st 5 days $400(80+80+80+80+80) after that 5 days (160+160+160+160+160)
after that (320+320+320.......15 times) =7600

hope above is clear.

regards
Malik Nadeem

"JE McGimpsey" wrote:

I'm obviously not understanding your specifications...

In your first example below, you say that 13 days should = 2160:

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

In your latest example, you say it should be 880.

B= 13
C=5
D=1
after using your formula in E1 column result showing 2160 instead of 880

Which is it and why?



In article ,
Malik Nadeem wrote:

Dear JE McGimpsey

below formula is not working properly see below examples

A= 08-JAN-2009
B= 6
C=5
D=1
after using your formula in E1 column result showing 560 instead of 80
====
example
====
A= 01-JAN-2009
B= 13
C=5
D=1
after using your formula in E1 column result showing 2160 instead of 880
=====
another example
=====
A= 08-JAN-2009
B= 6
C=5
D=1
her user formula working correctly showing result 7600 which i required
kindly advise same formula how work with above two examples
there are 3 slaps
one slap is 1-5 days
2nd slap is 6-10 days
3rd slap is 11-to 999

can i sent you the excel file ? if yes kindly advise your email address.

thanks in advnace for your great support and help

regards
Malik Nadeem







"JE McGimpsey" wrote:

One way:

E1: =IF(B1<=C1,"FREE",(B1+ MAX(B1-C1,0)+2*MAX(B1-2*C1,0))*D1*80)

However, I suspect you really need to use

B1: =TODAY()-A1

rather than

B1: =NOW()-A1

since NOW() includes the time, so

06-JAN 00:00:01 - 01-JAN

will return 5.000011574


In article ,
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

OK, I think

=MAX((B1-C1) + MAX(B1-2*C1,0) + 2*MAX(B1-3*C1,0),0)*D1*80

may be close to what you're looking for.

I'm probably still confused, though. You say:

1st 5 days $400(80+80+80+80+80) after that 5 days (160+160+160+160+160)
after that (320+320+320.......15 times) =7600

But

5*80 + 5*160 + 15*320 = 6000

I don't see how you got 7600...

What am I missing?






In article ,
Malik Nadeem wrote:

no sir,