if conditional formula required

[Malik Nadeem]

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

 

[sambhaji kuryal]

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

 

[JE McGimpsey]

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

 

[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]

I'm obviously not understanding your specifications...

In your first example below, you say that 13 days should = 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?

 

[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

 

[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]

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?

 

[Malik Nadeem]

yes sir you are right
the correct result against 25 days will be 6000 omsetead of 7600
now below formula is workable for me and its a great help also
can you please guide me from where i complete breife of MAX command

once against thankful to you for this support and help

regards
Malik Nadeem

 

[JE McGimpsey]

can you please guide me from where i complete breife of MAX command

XL Help.

 

[Malik Nadeem]

noted with thanks

XL Help.

 

[Malik Nadeem]

respected sir,

need your further assitance on this same fomula i have another question on
same formula i.e. =MAX((C4-D4) + MAX(C4-2*D4,0) + 2*MAX(C4-3*D4,0),0)*E4*80
now i want to change this formula like but when i m changing the charge
value its will not giving me the correct result OR not working properly
now my question is like this
charges per day of 1 LAP is 160 (from day 1st to 5th day)
2nd LAP is per day 320 (from day 6th to 10th days)
3rd slap is per day 640 ( from day 11 to ownworld for example 999)

how to change above fumula when i m using below it is not working
=MAX((C4-D4) + MAX(C4-2*D4,0) + 2*MAX(C4-3*D4,0),0)*E4*160

A4=10-JAN-2009
B4=28-JAN-2009
C4=19
D4=4
E4=1
F4=should be 5600 instead of 6400
is it posible kindly guide me i realy thankful to you.

but if d4 is grater then or equal to 5 its is counting/working properly.
if d4 value is less then 5 its not working properly.
can you please guide where i can change




regards
Malik Nadeem

 

[JE McGimpsey]

One way:

=MAX((C4-D4) + MAX(C4-D4-5,0) + 2*MAX(C4-D4-10,0),0)*E4*160

 

[Dana DeLouis]

Same thing, but would this idea work?

=MAX(80*A1, 160*A1-400,320*A1-2000)

5 -> 400
25 -> 6000

= = =
Dana DeLouis