SMALL function

  • Thread starter Thread starter like2hike
  • Start date Start date
L

like2hike

I will see if I can explain the situation clearly. I have a range of value
s 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 assign the value in a1 to 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.

A B C D E
1 50 100 20 80 200
2 50

In this example C1 is the smallest value in the range so it would be
assigned the value of 50. What is the formula to calculate the whole range?

I tried =if(small($b$1:$d$1,1)=true,$a$1,here things screw up)
 
You can't replace 20 with another value using functions so that the value in
C1 changes, you can "replace" it in another function like this

=SUM(SMALL(B1:D1,{2,3}),A1)

which will sum second and third smallest values plus A1
 
I think Peo means

=SUM(SMALL(B1:E1,{2,3,4}),A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Actually no. I saw the OP's formula B1:D1 so I assumed there were 3 values
and that
the OP wanted to replace the lowest value of those 3 with A1. I have no
clues if I was right or not.
God knows it's hard enough to get a clue from some posts.. OTOH in the table
there were values from A1 to E1 so you might be correct. Regardless the
method is the same just one more cell, a more generic approach albeit ugly
might be

=SUM(SMALL(OFFSET($B$1,,,,COUNT($B$1:$IV$1)),ROW(INDIRECT("2:"&COUNT($B$1:$I
V$1)))),A1)

entered with ctrl + shift & enter
 
Back
Top