# Thread: Calculation (Office 2000 SR-1)

1. ## Calculation (Office 2000 SR-1)

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

2. ## Re: Calculation (Office 2000 SR-1)

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.

3. ## Re: Calculation (Office 2000 SR-1)

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
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)

4. ## Re: Calculation (Office 2000 SR-1)

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.

5. ## Re: Calculation (Office 2000 SR-1)

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.

6. ## Re: Calculation (Office 2000 SR-1)

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

Andrew C.

7. ## Re: Calculation (Office 2000 SR-1)

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.

8. ## Re: Calculation (Office 2000 SR-1)

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

HansV,

10. ## Re: Calculation (Office 2000 SR-1)

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.

11. ## Re: Calculation (Office 2000 SR-1)

Jan Karel Pieterse,

12. ## Re: Calculation (Office 2000 SR-1)

In F2 enter:

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

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

13. ## Re: Calculation (Office 2000 SR-1)

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.

14. ## Re: Calculation (Office 2000 SR-1)

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!

15. ## Re: Calculation (Office 2000 SR-1)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•