This will work, put 0 in B5 and use

=SUMPRODUCT(--((C4-D4){0;5;10}), ((C4-D4)-{0;5;10}),ABS((B6:B8)-(B5:B7)))

--
__________________________________
HTH

Bob

"Malik Nadeem" wrote in message
...
yes its working good i need this result its great but i want to do some
little more changes in the cell C7

i want to only show value in C7=320 instead of C7=160
C6:c8 mean 160, 160, 640
when i putting in a c7=320 result change which is not correct for me
example
if
C4=7
D4=0
C6=160
C7=160
C8=640
=SUMPRODUCT(--((C4-D4){0;5;10}), ((C4-D4)-{0;5;10}), B6:B8)
result=1440 correct 100% correct answer

but i need like this
C4=1
D4=0
C6=160
C7=320
C8=640
=SUMPRODUCT(--((C4-D4){0;5;10}), ((C4-D4)-{0;5;10}), B6:B8)
result=1760 (wrong for me i want 1440)

is there any posiblity

kindly advise



"Bernard Liengme" wrote:

No, that will not work but this will:
=SUMPRODUCT(--((C4-D4){0,5,10}), ((C4-D4)-{0,5,10}), C6:E6)
Note the use of a HORIZONTAL range of THREE cells (any such range will
work)
You will fill these with 160, 160, 320 for your purpose
If you must use a vertical range, then try replacing the comma with the
braces by semi-colons
=SUMPRODUCT(--((C4-D4){0;5;10}), ((C4-D4)-{0;5;10}), C6:C8)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Malik Nadeem" wrote in message
...
Dear Bob Phillips

thanks for your below help kindly further advise can i convert last
"{160,160,320}" into a "{C6,C6,C8}"?

example
=SUMPRODUCT(--((C4-D4){0,5,10}), ((C4-D4)-{0,5,10}), {160,160,320})
=SUMPRODUCT(--((C4-D4){0,5,10}), ((C4-D4)-{0,5,10}), {C6,C6,C8})

when the value of C6=80 & c8=160

waiting for your usual support.

=+++++++++++++++++++++++++++++++++

"Bob Phillips" wrote:

=SUMPRODUCT(--((C4-D4){0,5,10}), ((C4-D4)-{0,5,10}), {160,160,320})

--
__________________________________
HTH

Bob

"Malik Nadeem" wrote in
message
...
need your assitance on this below fomula i have question on
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 but when i m changing the value
after
E4
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) in above
formula
80
2nd LAP is per day 320 (from day 6th to 10th days) in above formula
160
3rd slap is per day 640 ( from day 11 to ownworld for example 999)
in
....
320

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
total C4 is 19 days D4 is free days Left which is not visible i.e.
15
days
now we charge this 1st 5 days 5x160 2ndl lap from 6-10 days which
equal
to
320 per days and rest multiply with 640

remarks Note:
if d4 is grater then or equal to 5 its is working properly.
if d4 value is less then 5 its not giving correct value .
can you please guide how can i cahnge this one

thanks in advnace for support and help.

regards
Malik Nadeem