|
A CAP?
does anyone know how I can put a cap of 400 on this formula?
=IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)) Thanks so much and have... -- OneFineDay |
A CAP?
=MIN(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),400)
-- Gary''s Student - gsnu200849 "M Thompson" wrote: does anyone know how I can put a cap of 400 on this formula? =IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)) Thanks so much and have... -- OneFineDay |
A CAP?
=IF((-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02))<-400,-400,IF(H9="","",(-
MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)))) I couldn't make the value any greater than 400 but it could be less than -400 so the cap is at -400 I am sure that it is possible to make it shorter. edvwvw M Thompson wrote: does anyone know how I can put a cap of 400 on this formula? =IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)) Thanks so much and have... -- Message posted via http://www.officekb.com |
A CAP?
Your answer is definitely the shortest I've seen--thus the best for me. The
only problem is for some reason it is ignoring the "if" part. It's giving a "value" error until something is input into that cell. I got around it by putting another if statement in front again: =IF(H9="","",MAX(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),-400)). This works perfectly, but can I can't seem to get rid of the 2nd "if" and still make the whole thing work. Can it be modified and still work? By the way, as you'll notice, I mean't to say I needed a cap at -400. Thanks for taking the time to help. -- OneFineDay "Gary''s Student" wrote: =MIN(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),400) -- Gary''s Student - gsnu200849 "M Thompson" wrote: does anyone know how I can put a cap of 400 on this formula? =IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)) Thanks so much and have... -- OneFineDay |
A CAP?
=MIN(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),400)
Would this work? =MEDIAN(-400,-MIN(3 + 0.02* H9, 0.05* H9),0) HTH Dana DeLouis = = = M Thompson wrote: Your answer is definitely the shortest I've seen--thus the best for me. The only problem is for some reason it is ignoring the "if" part. It's giving a "value" error until something is input into that cell. I got around it by putting another if statement in front again: =IF(H9="","",MAX(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),-400)). This works perfectly, but can I can't seem to get rid of the 2nd "if" and still make the whole thing work. Can it be modified and still work? By the way, as you'll notice, I mean't to say I needed a cap at -400. Thanks for taking the time to help. |
A CAP?
I did a copy/paste into the cell, but it calculated everything at -400.
Thanks for spending the time on it, though. - OneFineDay "Dana DeLouis" wrote: =MIN(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),400) Would this work? =MEDIAN(-400,-MIN(3 + 0.02* H9, 0.05* H9),0) HTH Dana DeLouis = = = M Thompson wrote: Your answer is definitely the shortest I've seen--thus the best for me. The only problem is for some reason it is ignoring the "if" part. It's giving a "value" error until something is input into that cell. I got around it by putting another if statement in front again: =IF(H9="","",MAX(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),-400)). This works perfectly, but can I can't seem to get rid of the 2nd "if" and still make the whole thing work. Can it be modified and still work? By the way, as you'll notice, I mean't to say I needed a cap at -400. Thanks for taking the time to help. |
A CAP?
Hi. Are you sure you were changing H9?
I was also trying to point out a different technique for a Max(Min... combination. HTH :) Dana DeLouis M Thompson wrote: I did a copy/paste into the cell, but it calculated everything at -400. Thanks for spending the time on it, though. - OneFineDay "Dana DeLouis" wrote: =MIN(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),400) Would this work? =MEDIAN(-400,-MIN(3 + 0.02* H9, 0.05* H9),0) HTH Dana DeLouis = = = M Thompson wrote: Your answer is definitely the shortest I've seen--thus the best for me. The only problem is for some reason it is ignoring the "if" part. It's giving a "value" error until something is input into that cell. I got around it by putting another if statement in front again: =IF(H9="","",MAX(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),-400)). This works perfectly, but can I can't seem to get rid of the 2nd "if" and still make the whole thing work. Can it be modified and still work? By the way, as you'll notice, I mean't to say I needed a cap at -400. Thanks for taking the time to help. |
A CAP?
Well.......I sure am glad you were persistant! I tried it again, and it
worked. I added the if statement at the front, and everything went right. Thanks, for sure! By the by, would you mind explaining it to me? -- OneFineDay "Dana DeLouis" wrote: Hi. Are you sure you were changing H9? I was also trying to point out a different technique for a Max(Min... combination. HTH :) Dana DeLouis M Thompson wrote: I did a copy/paste into the cell, but it calculated everything at -400. Thanks for spending the time on it, though. - OneFineDay "Dana DeLouis" wrote: =MIN(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),400) Would this work? =MEDIAN(-400,-MIN(3 + 0.02* H9, 0.05* H9),0) HTH Dana DeLouis = = = M Thompson wrote: Your answer is definitely the shortest I've seen--thus the best for me. The only problem is for some reason it is ignoring the "if" part. It's giving a "value" error until something is input into that cell. I got around it by putting another if statement in front again: =IF(H9="","",MAX(IF(H9="","",-MAX(0,MIN(H9,100)*0.05)+-MAX(0,(H9-100)*0.02)),-400)). This works perfectly, but can I can't seem to get rid of the 2nd "if" and still make the whole thing work. Can it be modified and still work? By the way, as you'll notice, I mean't to say I needed a cap at -400. Thanks for taking the time to help. |
| All times are GMT +1. The time now is 04:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com