Calculation (Office 2000 SR-1)

Topic

New Reply

I can enter a few different numbers in one cell lined up vertically within the same cell by using

Reply | Quote

Replies

It can certainly done, but it is far from trivial. It involves defined names with XL4 macro functions or complicated array formula’s.

You can save yourself a lot of complexity by simply having each number in a single cell.

Reply | Quote

Jan,

I was fairly certain that would be the case but I thought there just might be a simple solution or function for doing it.

Thank you.

Reply | Quote

IMHO, what you’re doing is going against the grain of Excel. Different values belong in different cells. that makes formatting and calculations much easier.

Putting line feeds in a cell make it into text, so that numeric formatting will be ignored.

If you still want to do it as you designed, put the following function in a module:

Function CellSum(aCell As Range) As Double
Dim strVal As String
Dim intStart As Integer
Dim intEnd As Integer
strVal = aCell.Value
‘ Get out if cell is empty
If strVal = “” Then
Exit Function
End If
intStart = 1
‘ Look for line feed
intEnd = InStr(intStart, strVal, Chr$(10))
Do While intEnd > 0
‘ Add numeric value
CellSum = CellSum + Val(Mid(strVal, intStart, intEnd – intStart))
intStart = intEnd + 1
intEnd = InStr(intStart, strVal, Chr$(10))
Loop
‘ Add final (or only) part
CellSum = CellSum + Val(Mid(strVal, intStart))
End Function

Then, you can use a formula like

=A2*B2-CellSum(C2)

Reply | Quote

Hans,

Thank you very much. I may try it but I think your first suggestion of keeping things simple is probably best for me. However knowing me as I do, I will probably experiment with your module and see what kind of trouble I get myself into.

Many thanks.

Reply | Quote

To get the effect you are using in your sreadsheet, you could use merged cells. See attached.

Andrew C.

Reply | Quote

Andrew,

Thanks. I checked it out and see what you have done. It works as I wanted. I did, however already get HansV code working and it works real slick also.

I appreciate all the help. These forums are fantastic.

Reply | Quote

Hi hlewton,

My code was actually just a lark, to show that it could be done. However much you may like it, you still have the problem that a cell with line feeds is always considered to be text. You can’t format the individual numbers in it. So I think Andrew’s solution is to be preferred.

Regards,
Hans

Reply | Quote

HansV,

Thanks, I hadn

Reply | Quote

You should be aware however, that merged cells may raise all sorts of trouble in Excel, regarding copying, sorting, etcetera. Also, I was told some array formula’s will not work with merged cells (although you will probably never use them).

It might be simpler to make the sheet’s grid invisible (Tools, OPtions, View, Gridlines) and create a new “grid” using borders in the places you want them.

Reply | Quote

Jan Karel Pieterse,

Thank you. I hadn

Reply | Quote

As you noticed, sorting will give trouble with merged cells. It will cause you a lot of headaches trying to set things up with merged cells to be able to sort. Simplest is to NOT merge.

Another problem with your setup -if you want it sorted- is that for a proper sort you will need to have the name repeated accross each row. Otherwise the empty rows will end up at the bottom or the top of your sorted list, disconnected from the persons they belonged to!

Reply | Quote

Jan Karel Pieterse,

Thanks. I have been trying different sorts with the merged cells and, as you say, they do cause headaches. I think I

Reply | Quote

In F2 enter:

=A2*B2-SUM(EVAL(“{“&SUBSTITUTE(C2,CHAR(10),”,”)&”}”))

In order to use EVAL you need to download the Longre’s Morefunc add-in from:

http://longre.free.fr/english/index.html

Aladin

Reply | Quote

Aladin,

I have the functions downloaded and EVAL appears in the Addins, so they are now on my machine and; In F2 enter:
=A2*B2-SUM(EVAL(“{“&SUBSTITUTE(C2,CHAR(10),”,”)&”}”)) does the calculation just fine. Wish I understood more about these types of formulas.

Thanks.

Reply | Quote