Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell reference formula problem
Thanks guys! All your examples worked.
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem w/ cell reference | Excel Discussion (Misc queries) | |||
Named Formula reference problem | Excel Worksheet Functions | |||
Cell reference problem | Excel Worksheet Functions | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |