Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
Could you please explain what the formula should do.
This is what is does now =MAX((C4-D4) + MAX(C4-2*D4,0) + 2*MAX(C4-3*D4,0),0)*E4*160 =MAX( 15 + MAX(C4-2*D4,0) + 2*MAX(C4-3*D4,0),0)*E4*160 =MAX( 15 + 11 + 2*MAX(C4-3*D4,0),0)*E4*160 =MAX( 15 + 11 + 14,0)*E4*160 =MAX(40,0)*E4*160 =40*1*160 = 6400 How do you get 5600? -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
See a reply in the previous thread.
Please don't post the same question under two separate threads - first, it tends to fragment any answers you get, but it also wastes the time of those responding to a previously answered question. In article , Malik Nadeem wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
Bob,
I am very impressed that you could see this answers from the OP's question! Must be the UK beer that keeps the mind so alert! -- Bernard "Bob Phillips" wrote in message ... =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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
It keeps us something Bernard, but I can't remember what ...
-- __________________________________ HTH Bob "Bernard Liengme" wrote in message ... Bob, I am very impressed that you could see this answers from the OP's question! Must be the UK beer that keeps the mind so alert! -- Bernard "Bob Phillips" wrote in message ... =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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
{"Bernard Liengme" wrote==== =MAX((C4-D4)
it should be 19 instead of 15 beacuse C4=19 & D4=4 any how solved with the help of sumproduct formula thanks bop "Bernard Liengme" wrote: Could you please explain what the formula should do. This is what is does now =MAX((C4-D4) + MAX(C4-2*D4,0) + 2*MAX(C4-3*D4,0),0)*E4*160 =MAX( 15 + MAX(C4-2*D4,0) + 2*MAX(C4-3*D4,0),0)*E4*160 =MAX( 15 + 11 + 2*MAX(C4-3*D4,0),0)*E4*160 =MAX( 15 + 11 + 14,0)*E4*160 =MAX(40,0)*E4*160 =40*1*160 = 6400 How do you get 5600? -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
revised with corrected value of C6 & C8
sorry for inconvenience "Malik Nadeem" wrote: 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=160 & c8=320 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help on MAX
okay thanks bob
"Bob Phillips" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|