Cell reference formula problem
 

I have problems finding the correct formula for my Excel problem. My spread
sheet is used for statistics for my web shop.

In column A I have values representing sales figures for each order.
In column B I have a figure representing number of orders during a day.
These calculations work. Note that column B consists mostly of blanks.

In column C I want to calculate the turnover for all orders that day.

Example:

Col A Col B Col C
Row 3 150 4 1881
Row 4 143
Row 5 593
Row 6 995
Row 7 498 3 1056
Row 8 127
Row 9 431

For row 3 I tried with the following formula in cell C3:

=IF(ISBLANK(B3);"";SUM(A3:INDIRECT("A"&B3)))

In other words, if B3 is blank do nothing. If it contains a value then
SUM(A3:A6). But it didn't work properly. The sum function is not correct and
I get #Ref! error in column C for all rows containing blanks in column B.

I can see why because if I stand in C3 I want to sum A3:A3+3 or A3:A value
in B3 - 1. The #Ref! when standing in C4 is due to the blanks in column B,
which will give the sum function SUM(A4:INDIRECT("A").

Note that the values in columns A, B and C will be variable depending on the
attributes of the orders in my web shop.

Any help is appreciated.

Best

/Peter

Cell reference formula problem
 

Try this in C3 and copy down as needed:

=IF(B3="","",SUM(OFFSET(A3,,,B3)))

--
Biff
Microsoft Excel MVP


"Rocketeer" wrote in message
...
I have problems finding the correct formula for my Excel problem. My spread
sheet is used for statistics for my web shop.

In column A I have values representing sales figures for each order.
In column B I have a figure representing number of orders during a day.
These calculations work. Note that column B consists mostly of blanks.

In column C I want to calculate the turnover for all orders that day.

Example:

Col A Col B Col C
Row 3 150 4 1881
Row 4 143
Row 5 593
Row 6 995
Row 7 498 3 1056
Row 8 127
Row 9 431

For row 3 I tried with the following formula in cell C3:

=IF(ISBLANK(B3);"";SUM(A3:INDIRECT("A"&B3)))

In other words, if B3 is blank do nothing. If it contains a value then
SUM(A3:A6). But it didn't work properly. The sum function is not correct
and
I get #Ref! error in column C for all rows containing blanks in column B.

I can see why because if I stand in C3 I want to sum A3:A3+3 or A3:A value
in B3 - 1. The #Ref! when standing in C4 is due to the blanks in column B,
which will give the sum function SUM(A4:INDIRECT("A").

Note that the values in columns A, B and C will be variable depending on
the
attributes of the orders in my web shop.

Any help is appreciated.

Best

/Peter

Cell reference formula problem
 

In C3: =IF(B3="","",SUM(A3:INDIRECT("A"&ROW()-1+B3)))

copy down

or

In C3: =IF(B3="","",SUM(A3:INDEX(A3:$A$99,B3)))

copy down



"Rocketeer" wrote:

I have problems finding the correct formula for my Excel problem. My spread
sheet is used for statistics for my web shop.

In column A I have values representing sales figures for each order.
In column B I have a figure representing number of orders during a day.
These calculations work. Note that column B consists mostly of blanks.

In column C I want to calculate the turnover for all orders that day.

Example:

Col A Col B Col C
Row 3 150 4 1881
Row 4 143
Row 5 593
Row 6 995
Row 7 498 3 1056
Row 8 127
Row 9 431

For row 3 I tried with the following formula in cell C3:

=IF(ISBLANK(B3);"";SUM(A3:INDIRECT("A"&B3)))

In other words, if B3 is blank do nothing. If it contains a value then
SUM(A3:A6). But it didn't work properly. The sum function is not correct and
I get #Ref! error in column C for all rows containing blanks in column B.

I can see why because if I stand in C3 I want to sum A3:A3+3 or A3:A value
in B3 - 1. The #Ref! when standing in C4 is due to the blanks in column B,
which will give the sum function SUM(A4:INDIRECT("A").

Note that the values in columns A, B and C will be variable depending on the
attributes of the orders in my web shop.

Any help is appreciated.

Best

/Peter

Cell reference formula problem
 

=IF(B3="","",SUM(OFFSET(A3,,,B3)))

You might have to change the comma separators to semi-colons:

=IF(B3="";"";SUM(OFFSET(A3;;;B3)))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this in C3 and copy down as needed:

=IF(B3="","",SUM(OFFSET(A3,,,B3)))

--
Biff
Microsoft Excel MVP


"Rocketeer" wrote in message
...
I have problems finding the correct formula for my Excel problem. My
spread
sheet is used for statistics for my web shop.

In column A I have values representing sales figures for each order.
In column B I have a figure representing number of orders during a day.
These calculations work. Note that column B consists mostly of blanks.

In column C I want to calculate the turnover for all orders that day.

Example:

Col A Col B Col C
Row 3 150 4 1881
Row 4 143
Row 5 593
Row 6 995
Row 7 498 3 1056
Row 8 127
Row 9 431

For row 3 I tried with the following formula in cell C3:

=IF(ISBLANK(B3);"";SUM(A3:INDIRECT("A"&B3)))

In other words, if B3 is blank do nothing. If it contains a value then
SUM(A3:A6). But it didn't work properly. The sum function is not correct
and
I get #Ref! error in column C for all rows containing blanks in column B.

I can see why because if I stand in C3 I want to sum A3:A3+3 or A3:A
value
in B3 - 1. The #Ref! when standing in C4 is due to the blanks in column
B,
which will give the sum function SUM(A4:INDIRECT("A").

Note that the values in columns A, B and C will be variable depending on
the
attributes of the orders in my web shop.

Any help is appreciated.

Best

/Peter

Cell reference formula problem
 

Thanks guys! All your examples worked.

Cell reference formula problem
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rocketeer" wrote in message
...
Thanks guys! All your examples worked.