SMALL

[like2hike]

I will see if I can explain the situation clearly. I have a range of values
in b1:e1. There is also a value in a1. I want to evaluate the range of
numbers in b1:e1 and for the smallest value place the value in a1 in the row below it.
For the rest of the values in the range as they get larger I want to
calculate a PROPORTIONALLY lower value than the value that is in a1

I use the formula below to pick the largest value in a range and assign the value in a
to the row below it. The b1 is relative and changes as you move from column to colum
in the range. Howver, things do not work if you simply replace LARGE with SMALL

=IF(LARGE($b$1:$e$1,1)=TRUE,$a$1,ROUND($a$1*(b1/(LARGE($b$1:$e$1,1))),1))

 

[Peo Sjoblom]

Please post in the same thread where you started
Put this formula in B2 and copy across to E2

=IF(B1=MIN($B$1:$E$1),$A$1,"")


I don't understand what you mean by

"For the rest of the values in the range as they get larger I want to
calculate a PROPORTIONALLY lower value than the value that is in a1."

 

[HOGEY]

If I understand what you are trying to do, this may help

Try this in b2:e

=IF(B1=MIN($B$1:$E$1),$A$1,$A$1*(MIN(B1:E1)/B1)

----- like2hike wrote: ----

I will see if I can explain the situation clearly. I have a range of values
in b1:e1. There is also a value in a1. I want to evaluate the range of
numbers in b1:e1 and for the smallest value place the value in a1 in the row below it.
For the rest of the values in the range as they get larger I want to
calculate a PROPORTIONALLY lower value than the value that is in a1

I use the formula below to pick the largest value in a range and assign the value in a
to the row below it. The b1 is relative and changes as you move from column to colum
in the range. Howver, things do not work if you simply replace LARGE with SMALL

=IF(LARGE($b$1:$e$1,1)=TRUE,$a$1,ROUND($a$1*(b1/(LARGE($b$1:$e$1,1))),1))