| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 63858 | 2005-11-25 02:06:00 | Excel formula | rodb (1561) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 407317 | 2005-11-25 02:06:00 | 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. |
rodb (1561) | ||
| 407318 | 2005-11-25 02:49:00 | 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. |
Capt Jimbo (17) | ||
| 407319 | 2005-11-25 04:37:00 | 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. |
RogerRamjet (7055) | ||
| 407320 | 2005-11-25 07:21:00 | Thanks folks I'll have a look at the situation on Monday and see which suggestion works. |
rodb (1561) | ||
| 407321 | 2005-11-25 19:50:00 | 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 |
Parry (5696) | ||
| 1 | |||||