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

=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

=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

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

=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.

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.

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.

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.