two-column formula

**HR157** · 01-13-2010, 10:22 AM

How I can calculate two different columns one for dollars and one for cents?
I need to create a data base like an old accounting table.

**Richard Buttrey** · 01-13-2010, 10:29 AM

Hi

It's not quite clear what your aim is. Why do you actually need two columns rather than the normal single column formatted to show 2 places for cents?

However you have created two columns in which you are recording dollars and cents so what more do you want to do?

You mention calculating but there are no calculations on the sheet. What do you mean by calculate?

Rgds

**scaffdog845** · 01-13-2010, 10:37 AM

As rich has stated you would probably be better of merging the cells for your Dollars and Cents into a single cell using accounting format set to 2 decimal places. I think the trouble with you current layout would be getting the whole Dollar amount from the Cents column to carry over and add to the total in your Dollars column.

**HR157** · 01-13-2010, 10:59 AM

Originally Posted by Richard Buttrey

Hi

It's not quite clear what your aim is. Why do you actually need two columns rather than the normal single column formatted to show 2 places for cents?

However you have created two columns in which you are recording dollars and cents so what more do you want to do?

You mention calculating but there are no calculations on the sheet. What do you mean by calculate?

Rgds

I need to duplicate an old accounting sheet and print the resulting sum in two different columns (one for dollars and one for cents). Sheet must be like the original, but at the same time make the sum of both columns and result in separate columns.

**Andy Pope** · 01-13-2010, 11:38 AM

One way.

H29: =SUM(H10:H24)+(SUM(I10:I24)/100)-(I30/100)

I29: =((SUM(I10:I24)/100)-INT(SUM(I10:I24)/100))*100

**scaffdog845** · 01-13-2010, 11:53 AM

I think I may have worked it out. Thouroughly test the attached WB to make sure it calculates properly using several test sets of numbers. I tested and it seems to work fine.

I'm sure one of the Gurus may have a better solution, but I had fun trying to figure it out.

**scaffdog845** · 01-13-2010, 12:14 PM

Andy

I thought about using INT but abandoned the idea since the values in I:I were not actual decimal values. Sometimes the simplest things escape you. I didn't think to do the math first then use INT.

What took me 1/2 hour of toying with you probably had done in no time flat.

As NBVC likes to say

Where there is a will there are many ways. Pick One!