|
Help creating continuously calculating and updating formula
I'm not sure if I'm asking this correctly, so please help me clarify if needed!
first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. |
Help creating continuously calculating and updating formula
If there's no other numerical data beyond the last entry to the end of the
row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. |
Help creating continuously calculating and updating formula
=SUM(AI8:INDEX(AN8:AP8,1,AH8))
where AN8:AP8 is the range of extension columns (adjust to the real column numbers, e.g. to AQ8 or AR8, etc. AH8 contains the actual column number in the above range, e.g. 1 for AN, 2 for AO, 3 for AP. Replace it by the place where actual sum range is defined! Regards, Stefi €˛sjm182€¯ ezt Ć*rta: I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. |
Help creating continuously calculating and updating formula
Wow, I am so sorry that I don't understand this... I've been trying so hard
to make sense of it (and here I thought I was pretty fluent with Excel...). It just doesn't work for me. I'll just be honest and tell you that I don't know what you mean by the "range of extension columns" - is that meant to encompass all of the cells that are eventually going to be filled in? And I have no idea what you mean by the "actual column number" or what the actual sum range is. Perhaps it would help if I posted a formula using more basic cells/column identifiers. I am using this formula to keep a running total of hours: =SUM(C4:F4)-SUM(C3:F3) where I want to add data to row 3 - it will move to encompass G3, H3, etc. Here is the formula I am using to continuously tally % fulfillment (average): =SUM(C3:F3)/SUM(C4:F4) where I want to, as above, add data to row 3 I would like to be able to use a formula that does not require me to change the F column in the actual formula to G, H, etc. and will continuously update and give me a non-error answer. I'm very sorry if your answer below already solved this, I just really don't have any idea how to manipulate it to work!! Thanks! "Stefi" wrote: =SUM(AI8:INDEX(AN8:AP8,1,AH8)) where AN8:AP8 is the range of extension columns (adjust to the real column numbers, e.g. to AQ8 or AR8, etc. AH8 contains the actual column number in the above range, e.g. 1 for AN, 2 for AO, 3 for AP. Replace it by the place where actual sum range is defined! Regards, Stefi €˛sjm182€¯ ezt Ć*rta: I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. |
Help creating continuously calculating and updating formula
Thanks, I tried that, but because there is data in the bottom row, but not
the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . |
Help creating continuously calculating and updating formula
because there is data in the bottom row,
but not the top, the answer it returns is incorrect. I don't understand how that could cause the formula to return an incorrect result? -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Thanks, I tried that, but because there is data in the bottom row, but not the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . |
Help creating continuously calculating and updating formula
I am figuring a running total where the bottom row is the required hours
(known ahead of time) and the top row is actual hours (entered daily). If I include all of the cells in the range with a blank on the top row, it totals the % hours fulfilled wrong because it looks as if all the blank rows are zeros; I'm also figuring a running total of the difference between the two totals as the hours are filled and this is also wrong if it counts the blanks. Does that make sense? I think I'm being quite difficult without meaning to be :-) "T. Valko" wrote: because there is data in the bottom row, but not the top, the answer it returns is incorrect. I don't understand how that could cause the formula to return an incorrect result? -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Thanks, I tried that, but because there is data in the bottom row, but not the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . . |
Help creating continuously calculating and updating formula
Does that make sense?
SUM ignores empty cells so how do the empty cells affect the formula? If you have: 10...5...2...empty...empty...empty...empty to the end of the row The sum of that row is 17 no matter how many empty cells there are after the last entry. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I am figuring a running total where the bottom row is the required hours (known ahead of time) and the top row is actual hours (entered daily). If I include all of the cells in the range with a blank on the top row, it totals the % hours fulfilled wrong because it looks as if all the blank rows are zeros; I'm also figuring a running total of the difference between the two totals as the hours are filled and this is also wrong if it counts the blanks. Does that make sense? I think I'm being quite difficult without meaning to be :-) "T. Valko" wrote: because there is data in the bottom row, but not the top, the answer it returns is incorrect. I don't understand how that could cause the formula to return an incorrect result? -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Thanks, I tried that, but because there is data in the bottom row, but not the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . . |
Help creating continuously calculating and updating formula
True, but if the bottom cells have data in them, it will divide the 2 sums
giving an inaccurate number based on the current data. I think I'm asking too much of Excel, but thanks for the attempts! "T. Valko" wrote: Does that make sense? SUM ignores empty cells so how do the empty cells affect the formula? If you have: 10...5...2...empty...empty...empty...empty to the end of the row The sum of that row is 17 no matter how many empty cells there are after the last entry. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I am figuring a running total where the bottom row is the required hours (known ahead of time) and the top row is actual hours (entered daily). If I include all of the cells in the range with a blank on the top row, it totals the % hours fulfilled wrong because it looks as if all the blank rows are zeros; I'm also figuring a running total of the difference between the two totals as the hours are filled and this is also wrong if it counts the blanks. Does that make sense? I think I'm being quite difficult without meaning to be :-) "T. Valko" wrote: because there is data in the bottom row, but not the top, the answer it returns is incorrect. I don't understand how that could cause the formula to return an incorrect result? -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Thanks, I tried that, but because there is data in the bottom row, but not the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . . . |
Help creating continuously calculating and updating formula
I'm pretty sure we can solve this.
Can you post a small snippet of data and tell me what result you expect. Be very specific as to where the data is located. The more details you provide the easier it is for us to understand what you're trying to do. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... True, but if the bottom cells have data in them, it will divide the 2 sums giving an inaccurate number based on the current data. I think I'm asking too much of Excel, but thanks for the attempts! "T. Valko" wrote: Does that make sense? SUM ignores empty cells so how do the empty cells affect the formula? If you have: 10...5...2...empty...empty...empty...empty to the end of the row The sum of that row is 17 no matter how many empty cells there are after the last entry. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I am figuring a running total where the bottom row is the required hours (known ahead of time) and the top row is actual hours (entered daily). If I include all of the cells in the range with a blank on the top row, it totals the % hours fulfilled wrong because it looks as if all the blank rows are zeros; I'm also figuring a running total of the difference between the two totals as the hours are filled and this is also wrong if it counts the blanks. Does that make sense? I think I'm being quite difficult without meaning to be :-) "T. Valko" wrote: because there is data in the bottom row, but not the top, the answer it returns is incorrect. I don't understand how that could cause the formula to return an incorrect result? -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Thanks, I tried that, but because there is data in the bottom row, but not the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . . . |
Help creating continuously calculating and updating formula
Here is a portion of my table:
1 A B C D E F G H 2 58% 3.75 3 Completed 1 1 0 0.25 3 4 prescribed 2 2 2 1.5 1.5 1.5 5 50% 50% 0% 17% 200% Cell A2 has the formula: =SUM(C3:G3)/SUM(C4:G4) with the goal of finding the % fulfillment of hours completed/prescribed (using only data inputted, in this case up to column G). If I included the blank spaces as well (column H), then the % fulfilled to date would be 50%, which is not accurate. Cell B2 has the formula: =SUM(C4:G4)-SUM(C3:G3) with the goal of finding the # of hours left to fulfill (again using only data inputted). If I included column H, the total hours remaining would be 5.25, which again is not accurate. I'm trying to make a worksheet that employees can use to track this info and not have to continuously update the formula to extend to the next column. Honestly, I'm thinking the easiest way to do it is just to input the row 3 and 4 info each week and have the formula extend to the end of the period. It would be ideal if the prescribed hours could all be filled in advance, though. "T. Valko" wrote: I'm pretty sure we can solve this. Can you post a small snippet of data and tell me what result you expect. Be very specific as to where the data is located. The more details you provide the easier it is for us to understand what you're trying to do. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... True, but if the bottom cells have data in them, it will divide the 2 sums giving an inaccurate number based on the current data. I think I'm asking too much of Excel, but thanks for the attempts! "T. Valko" wrote: Does that make sense? SUM ignores empty cells so how do the empty cells affect the formula? If you have: 10...5...2...empty...empty...empty...empty to the end of the row The sum of that row is 17 no matter how many empty cells there are after the last entry. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I am figuring a running total where the bottom row is the required hours (known ahead of time) and the top row is actual hours (entered daily). If I include all of the cells in the range with a blank on the top row, it totals the % hours fulfilled wrong because it looks as if all the blank rows are zeros; I'm also figuring a running total of the difference between the two totals as the hours are filled and this is also wrong if it counts the blanks. Does that make sense? I think I'm being quite difficult without meaning to be :-) "T. Valko" wrote: because there is data in the bottom row, but not the top, the answer it returns is incorrect. I don't understand how that could cause the formula to return an incorrect result? -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Thanks, I tried that, but because there is data in the bottom row, but not the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . . . . |
Help creating continuously calculating and updating formula
Ok, if I understand, then you only want to use cells in both rows that have
data entered. If this was your data: ...........C..........D 3........1............. 4........2...........1 Then D4 should be excluded from the calculation. Is that correct? If so, try these: Assuming the potential end of the range is column AA. Formula for A2: =SUM(C3:AA3)/SUMIF(C3:AA3,"<",C4:AA4) Formula for B2: =SUMIF(C3:AA3,"<",C4:AA4)-SUM(C3:AA3) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Here is a portion of my table: 1 A B C D E F G H 2 58% 3.75 3 Completed 1 1 0 0.25 3 4 prescribed 2 2 2 1.5 1.5 1.5 5 50% 50% 0% 17% 200% Cell A2 has the formula: =SUM(C3:G3)/SUM(C4:G4) with the goal of finding the % fulfillment of hours completed/prescribed (using only data inputted, in this case up to column G). If I included the blank spaces as well (column H), then the % fulfilled to date would be 50%, which is not accurate. Cell B2 has the formula: =SUM(C4:G4)-SUM(C3:G3) with the goal of finding the # of hours left to fulfill (again using only data inputted). If I included column H, the total hours remaining would be 5.25, which again is not accurate. I'm trying to make a worksheet that employees can use to track this info and not have to continuously update the formula to extend to the next column. Honestly, I'm thinking the easiest way to do it is just to input the row 3 and 4 info each week and have the formula extend to the end of the period. It would be ideal if the prescribed hours could all be filled in advance, though. "T. Valko" wrote: I'm pretty sure we can solve this. Can you post a small snippet of data and tell me what result you expect. Be very specific as to where the data is located. The more details you provide the easier it is for us to understand what you're trying to do. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... True, but if the bottom cells have data in them, it will divide the 2 sums giving an inaccurate number based on the current data. I think I'm asking too much of Excel, but thanks for the attempts! "T. Valko" wrote: Does that make sense? SUM ignores empty cells so how do the empty cells affect the formula? If you have: 10...5...2...empty...empty...empty...empty to the end of the row The sum of that row is 17 no matter how many empty cells there are after the last entry. -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I am figuring a running total where the bottom row is the required hours (known ahead of time) and the top row is actual hours (entered daily). If I include all of the cells in the range with a blank on the top row, it totals the % hours fulfilled wrong because it looks as if all the blank rows are zeros; I'm also figuring a running total of the difference between the two totals as the hours are filled and this is also wrong if it counts the blanks. Does that make sense? I think I'm being quite difficult without meaning to be :-) "T. Valko" wrote: because there is data in the bottom row, but not the top, the answer it returns is incorrect. I don't understand how that could cause the formula to return an incorrect result? -- Biff Microsoft Excel MVP "sjm182" wrote in message ... Thanks, I tried that, but because there is data in the bottom row, but not the top, the answer it returns is incorrect. I dont' even know if what I"m asking is possible - I want to create a form that I can share with others to use, but I dont' want to have to instruct them to update the formula each time you add in a new value. "T. Valko" wrote: If there's no other numerical data beyond the last entry to the end of the row just expand the range: =SUM(AI49:IV49)-SUM(AI48:IV48) -- Biff Microsoft Excel MVP "sjm182" wrote in message ... I'm not sure if I'm asking this correctly, so please help me clarify if needed! first example: =SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly extended (AO, AP, etc.). How can I create a formula that gives me an answer when cell 49 is standard and cell 48 is continuously updating? Currently, I am manually changing "AN" to "AO", and so on as I update the information. second example: =SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as above. . . . . |
| All times are GMT +1. The time now is 07:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com