PDA

View Full Version : Excel formula



rodb
25-11-2005, 02:06 PM
Maybe I'm just thick today, but I can't think of the answer!!
If I have 6 columns (say B2, D2, G2, I2, L2, N2) with a value in each, and I want to produce the total of the 4 smallest values out of the 6, what is the formula? And is there any complication if one of the numbers to be included is a zero?
Many thanks to anyone who can help.

Capt Jimbo
25-11-2005, 02:49 PM
My version of Excel (2003) has a function called "Small" which would appear to be able to achieve what you want.
Sorry but I'm not sure if this was included in earlier versions.

RogerRamjet
25-11-2005, 04:37 PM
I would use the formula =SUM(B2:N2)-LARGE(B2:N2,1)-LARGE(B2:N2,2) The 1 in LARGE(B2:N2,1) gives the largest value and 2 gives the 2nd largest.

The formula totals all the values and subtracts the 2 largest values. The only problem would be if there are values in the the other cells in the range ie C2,E2,F2,H2,J2 etc in which case you would append -C2-E2 etc to the formula.

Hope this helps.

rodb
25-11-2005, 07:21 PM
Thanks folks

I'll have a look at the situation on Monday and see which suggestion works.

Parry
26-11-2005, 07:50 AM
Hi another alternative would be to combine small and sum in one formula. The 1-4 in {} are the smallest ranked values so this is creating an array of the four lowest values and then using sum to total. Note this is just a standard formula and not array entered - you will need to include the curly braces.

=SUM(SMALL(B2:N2,{1,2,3,4}))

regards,
Graham