Formula Question

  • Thread starter Thread starter CoconutCarl
  • Start date Start date
C

CoconutCarl

Hi,

I have a list of numbers seperated by spaces [A]: Next to SOME of those
numbers are other numbers [B}. The second set of numbers on the right
represent hours. All data between two spaces represents a different day. In a
third column [C], at the bottom, I want to total the hours that are in the
second column, so I know how many hours were worked for each day. (As in
parenthesis in the example below.) Is there a way to do this without using a
macro?

I hope this was clear.

Thanks,

CC


A B C

4 4
5 2
6 (6)

2 3
3 (3)

1 7
2 (7)

2 3
5
6
8 7
5 (10)
 
CoconutCarl said:
Hi,

I have a list of numbers seperated by spaces [A]: Next to SOME of
those numbers are other numbers [B}. The second set of numbers on the
right represent hours. All data between two spaces represents a
different day. In a third column [C], at the bottom, I want to total
the hours that are in the second column, so I know how many hours were
worked for each day. (As in parenthesis in the example below.) Is
there a way to do this without using a macro?

I hope this was clear.

Thanks,

CC


A B C

4 4
5 2
6 (6)

2 3
3 (3)

1 7
2 (7)

2 3
5
6
8 7
5 (10)
Click to expand...

One way is to use two helper columns.

In D1, put
=SUM(B$1:B1)
and copy down for as many rows as you need.

In E2, put
=IF(A2="", D2-SUM(E$1:E1), "")
and copy down for as many rows as you need.

In C2, put
=IF(E2="","","("&E2&")")
and copy down for as many rows as you need.

The general idea is:

Column D is a running total of all hours.

Column E is the current subtotal at the end of each day.
It's column D minus the sum of the previous subtotals.

Column C has the number in Column E, but with the parentheses
you want.
 
Hi
and how do you identify in which row to calculate the subtotal?
Click to expand...

I'm not sure I understand your question, so I will try to simplify mine.

Let's say you have the folllowing numbers in a column:

6
7
8
45
6
7

Now the number 6 represents $6 dollars earned and next to the six I put in the
number of hours (minutes) it took me to make that $6.

6 (1.5)

Now the 7, 8 and 45 represent dollars earned doing three different things but
at the same time, so the number next to 45 represents the total hours spent
earning that money. That's why there isn't a number for hours next to every
number in the column.

6 1.0
7
8
45 2.5
6
7 4.25

And finally I worked 4.25 hours and earned $6 and $7 respectively. I input
tomorrow's data the same way only a space seperates yesterday's numbers from
today's and so on.

Next to the 4.25, in a third column, I want a total of the hours worked for the
day, which would be 1.0 + 2.5 + 4.25 or 8.

6 1.0
7
8
45 2.5
6
7 4.25 8

Now, when I look at my spreadsheet, I can see at a glance how many hours I
worked on each day. The column to the left of that shows how many hours I
worked for each session on that day.

The tricky part is totaling the numbers in the second column between the spaces
for each day. I'm pretty sure this can be done with one formula, but if it
can't I'll use the other posters suggestion and add in some helper columns.

Thanks,

CC
CoconutCarl said:
Hi,

I have a list of numbers seperated by spaces [A]: Next to SOME of those
numbers are other numbers [B}. The second set of numbers on the right
represent hours. All data between two spaces represents a different day. In a
third column [C], at the bottom, I want to total the hours that are in the
second column, so I know how many hours were worked for each day. (As in
parenthesis in the example below.) Is there a way to do this without using a
macro?

I hope this was clear.

Thanks,

CC


A B C

4 4
5 2
6 (6)

2 3
3 (3)

1 7
2 (7)

2 3
5
6
8 7
5 (10)
Click to expand...
Click to expand...
 
Hi
do you also have a column with the date?

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
and how do you identify in which row to calculate the subtotal?
Click to expand...

I'm not sure I understand your question, so I will try to simplify
mine.

Let's say you have the folllowing numbers in a column:

6
7
8
45
6
7

Now the number 6 represents $6 dollars earned and next to the six I
put in the number of hours (minutes) it took me to make that $6.

6 (1.5)

Now the 7, 8 and 45 represent dollars earned doing three different
things but at the same time, so the number next to 45 represents the
total hours spent earning that money. That's why there isn't a
number for hours next to every number in the column.

6 1.0
7
8
45 2.5
6
7 4.25

And finally I worked 4.25 hours and earned $6 and $7 respectively. I
input tomorrow's data the same way only a space seperates yesterday's
numbers from today's and so on.

Next to the 4.25, in a third column, I want a total of the hours
worked for the day, which would be 1.0 + 2.5 + 4.25 or 8.

6 1.0
7
8
45 2.5
6
7 4.25 8

Now, when I look at my spreadsheet, I can see at a glance how many
hours I worked on each day. The column to the left of that shows how
many hours I worked for each session on that day.

The tricky part is totaling the numbers in the second column between
the spaces for each day. I'm pretty sure this can be done with one
formula, but if it can't I'll use the other posters suggestion and
add in some helper columns.

Thanks,

CC
CoconutCarl said:
Hi,

I have a list of numbers seperated by spaces [A]: Next to SOME of those
numbers are other numbers [B}. The second set of numbers on the right
represent hours. All data between two spaces represents a
Click to expand...
different
day. In a
third column [C], at the bottom, I want to total the hours that are in the
second column, so I know how many hours were worked for each day. (As in
parenthesis in the example below.) Is there a way to do this
without using a
macro?

I hope this was clear.

Thanks,

CC


A B C

4 4
5 2
6 (6)

2 3
3 (3)

1 7
2 (7)

2 3
5
6
8 7
5 (10)
Click to expand...
Click to expand...
Click to expand...
 
Hi
do you also have a column with the date?

Regards
Frank Kabel
Click to expand...

Yes, I do.

CC
Hi
and how do you identify in which row to calculate the subtotal?
Click to expand...

I'm not sure I understand your question, so I will try to simplify
mine.

Let's say you have the folllowing numbers in a column:

6
7
8
45
6
7

Now the number 6 represents $6 dollars earned and next to the six I
put in the number of hours (minutes) it took me to make that $6.

6 (1.5)

Now the 7, 8 and 45 represent dollars earned doing three different
things but at the same time, so the number next to 45 represents the
total hours spent earning that money. That's why there isn't a
number for hours next to every number in the column.

6 1.0
7
8
45 2.5
6
7 4.25

And finally I worked 4.25 hours and earned $6 and $7 respectively. I
input tomorrow's data the same way only a space seperates yesterday's
numbers from today's and so on.

Next to the 4.25, in a third column, I want a total of the hours
worked for the day, which would be 1.0 + 2.5 + 4.25 or 8.

6 1.0
7
8
45 2.5
6
7 4.25 8

Now, when I look at my spreadsheet, I can see at a glance how many
hours I worked on each day. The column to the left of that shows how
many hours I worked for each session on that day.

The tricky part is totaling the numbers in the second column between
the spaces for each day. I'm pretty sure this can be done with one
formula, but if it can't I'll use the other posters suggestion and
add in some helper columns.

Thanks,

CC
Hi,

I have a list of numbers seperated by spaces [A]: Next to SOME of
those
numbers are other numbers [B}. The second set of numbers on the
right
represent hours. All data between two spaces represents a different
day. In a
third column [C], at the bottom, I want to total the hours that are
in the
second column, so I know how many hours were worked for each day.
(As in
parenthesis in the example below.) Is there a way to do this
without
using a
macro?

I hope this was clear.

Thanks,

CC


A B C

4 4
5 2
6 (6)

2 3
3 (3)

1 7
2 (7)

2 3
5
6
8 7
5 (10)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top