PDA

View Full Version : Solved: Adding only certain values within a cell



Nick07
01-15-2009, 02:10 PM
Hello :hi:,

I am trying to solve a problem in VBA that is well beyond my knowledge so I am posting here in the hope that somebody may be able to help.

I have a column in Excel, say Column A, that contains either a one, two or three digit value or, on very rare occasions, no value at all. I would like to be able to sum the two smallest values in this cell and have the results appear in Column B. In the event that there is only one digit in the relevant cell then Column B should just hold that value. If column A is empty then Column B should be empty too. For example:

Column A Column B (required value)
171 ------ 2
11 ------- 2
293 ------ 5
452 ------ 6
111 ------ 2
112 ------ 2
62 ------- 8
2 -------- 2
718 ------ 8
264 ------ 6
334 ------ 6
-------
529 ------ 7
718 ------ 8
21 ------- 3

Any help would be greatly appreciated.

All the best,
Nick

Bob Phillips
01-15-2009, 04:43 PM
You can do it without VBA, using this array formula

=IF(A1="","",IF(LEN(A1)=1,A1,SUM(SMALL(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),{1,2}))))

slamet Harto
01-16-2009, 11:12 PM
Excellent Bob, it is using CSE right? =IF(A1="","",IF(LEN(A1)=1,A1,SUM(SMALL(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),{1,2}))))} can you explain what {1,2} is?

Bob Phillips
01-17-2009, 03:59 AM
Hi Harto,

{1,2} is an array constant. In an array formula, each element of an array constant is evaluated separately against the condition, in the same way that each element of a range array is.

In this case, the OP wanted the two smallest values contained within the cell. This part
--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
gets an array of all of the values in the cell, then we use SMALL to get the smallest, passing the array constant {1,2} so as to get the 1st and 2nd smallest.

I could have put the values 1 and 2 in say cells M1:M2 and used M1:M2 in the formula instead, which I would do for many values, but it seemed okay for just two.

Nick07
01-17-2009, 06:52 AM
Hi, thanks very much for the formula.

When I paste the formula into my worksheet I get the #NUM! error. The values in Column A are in the General format and I have copied the formula exactly. Do you know what's causing this?

Thanks,
Nick

Nick07
01-17-2009, 07:06 AM
Sorry, I was being a Muppet!

I didn't know how to enter array formulas into a spreadsheet (i.e. you have to use CSE).

It amazes me what can be achieved in Excel if you know what you are doing. This is going to save me so much work.

Many thanks for your help and all the best.

Nick

slamet Harto
01-17-2009, 07:10 AM
Hi Nick

When you have enter the formula then you have to send "Ctrl+Shift+Enter"
along in it.

Hi Bob, really appreciated it. As always thank you.
Best,
Harto