Sum within a single Cell?

[Cyberseeker]

This may sound a little odd, but I would like a single cell to display two figures in the following format: + 19 + 1 Both plus (+) signs need to be displayed.

Then, in an adjacent cell I would like to sum the first cell and divide it thus: =B69/4 The answer in this hypothetical example should be "5", but no plus sign should appear in this case.

My efforts today give me a result of, "#value" Can I get it to do what I want?



Thanks for any help.

[Pecoflyer]

As the two figures in one cell constitute a text string, VBA will probably be needed.
What are you trying to do?

[Cyberseeker]

Yes, I do want it to read like text, but I also want it to sum in another column. If VBA is simply achieved I might give it a go, but if it's too complicated I may have to just write the answer manually.

[jeffreybrown]

Maybe, with + 19 + 1 in A1...

=SUM((TRIM(MID(SUBSTITUTE(A1,"+",REPT(" ",255)),1+(ROW(A1:A200)-1)*255,255)) & "0")/10)

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

[Cyberseeker]

That worked well Jeffrey, but I omitted mentioning in the opening post that I wanted to add another cell at the same time. So, I inserted it into your code as follows:

=(SUM((TRIM(MID(SUBSTITUTE(B75,"+",REPT(" ",255)),1+(ROW(A1:A200)-1)*255,255)) & "0")/10)+SUM((TRIM(MID(SUBSTITUTE(B76,"+",REPT(" ",255)),1+(ROW(A1:A200)-1)*255,255)) & "0")/10))/29.5306

I suspect that my code is unnecessarily longwinded because there are two numbers in the first cell but the second cell only contains one figure. Can you condense it for me?

Incidentally, my project relates to a soli-lunar calendar.

[jeffreybrown]

Please provide what exactly is in B75 and what is in B76!

Also, what do you expect as the answer?

My thought, use the formula provided for answers in two separate cells and then as an example...

=(C75+C76)/29.5306

If there is only one number in B76, then not sure we need the first formula, but once you tell us what is in B76 then we can get something to work.

[Cyberseeker]

B75 reads "+1279 + 1"
B76 reads "+1290"

F75 should read "87 months" in other words, 1279 days, plus 1 day, plus 1290 days equals 2570 days. Divide that by a lunar month (29.5306 days) equals 87.0284 months.

I have custom formatted F75 to leave out the decimal points and to place "87 months" but I cannot automatically add up to 87.

Also, I need to fill down the "F" column so that this formula keeps repeating adjacent to its respective cells in "B" column.

[jeffreybrown]

Try...

       ----B---- C D E ----F----
  75   +1279 + 1       87 Months
  76   +1290

F75 =(SUM((TRIM(MID(SUBSTITUTE(B75,"+",REPT(" ",255)),1+(ROW($A$1:$A$200)-1)*255,255)) & "0")/10)+(B76+0))/29.5306

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

Custom Format >> F75

• Right click cell (or Ctrl + 1)
• Format Cells
• Number
• Custom
• Type: 0" Months"

Also, I need to fill down the "F" column so that this formula keeps repeating adjacent to its respective cells in "B" column.

If the current values are in B75 and B76 with the result in F75, where are the next values, B77 and B78?

If so, you can just copy F75 and paste into F77.

How far down do you need to copy? How many results did you need?

[Cyberseeker]

Originally Posted by jeffreybrown

If the current values are in B75 and B76 with the result in F75, where are the next values, B77 and B78?

B78 and B79 (B77 is missed out)

Originally Posted by jeffreybrown

How far down do you need to copy? How many results did you need?

Its open-ended, however Ill probably stop at 210 results.

Thanks for your good help. I have filled down for 50 results and it works like a charm.

[jeffreybrown]

You are most welcome and thanks for the feedback.

If you are going to have about 210 instances of the formula, you should adjust...

ROW($A$1:$A$200) >> to >> ROW($A$1:$A$300)

300 just puts you on the safe side.