|
New Excel function SUMABOVE
Suggested new Excel function: SUMABOVE(Ref)
Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
=SUM(D1:INDEX(D:D,3))
Is the equivalent of: =SUM(D1:D3) Or, using A1 to hold the variable: A1 = 3 =SUM(D1:INDEX(D:D,A1)) Note: if A1 is empty or 0 all of column D will be summed. Biff "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
=SUM(D1:OFFSET(D4,-1,0))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Don Guillett" wrote in message ... try this =SUM(INDIRECT("A4:a"&ROW()-1)) -- Don Guillett SalesAid Software "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
not automatic on row insertion
-- Don Guillett SalesAid Software "Biff" wrote in message ... =SUM(D1:INDEX(D:D,3)) Is the equivalent of: =SUM(D1:D3) Or, using A1 to hold the variable: A1 = 3 =SUM(D1:INDEX(D:D,A1)) Note: if A1 is empty or 0 all of column D will be summed. Biff "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
Oh, I wasn't paying attention!
Biff "Don Guillett" wrote in message ... not automatic on row insertion -- Don Guillett SalesAid Software "Biff" wrote in message ... =SUM(D1:INDEX(D:D,3)) Is the equivalent of: =SUM(D1:D3) Or, using A1 to hold the variable: A1 = 3 =SUM(D1:INDEX(D:D,A1)) Note: if A1 is empty or 0 all of column D will be summed. Biff "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
No offense, guys, but do you think anyone who can write those functions
(which yes, I can) is going to forget to modify the sum function when a new row is added? I'm looking for a relatively simple solution which can be applied by people who are generally going to be less knowledgable than those on this forum. (I'm sitting at a client corporation right now fixing spreadsheets that have this problem, as well as some of the ugly but traditional workarounds such as blank lines that create their own sets of issues.) Cheers. ps please nobody be offended by the Disagree buttons that I clicked. I thought that would bring up a comment section -- it took a while to spot the Reply button. "Don Guillett" wrote: try this =SUM(INDIRECT("A4:a"&ROW()-1)) -- Don Guillett SalesAid Software "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
Simple you want, simple you get
Put this in a regular module in the bottom cell just type =sa() Public Function sa() Application.Volatile lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row - 1 mc = ActiveCell.Column sa = Application.Sum _ (Range(Cells(1, mc), Cells(lr, mc))) End Function -- Don Guillett SalesAid Software "brent" wrote in message ... No offense, guys, but do you think anyone who can write those functions (which yes, I can) is going to forget to modify the sum function when a new row is added? I'm looking for a relatively simple solution which can be applied by people who are generally going to be less knowledgable than those on this forum. (I'm sitting at a client corporation right now fixing spreadsheets that have this problem, as well as some of the ugly but traditional workarounds such as blank lines that create their own sets of issues.) Cheers. ps please nobody be offended by the Disagree buttons that I clicked. I thought that would bring up a comment section -- it took a while to spot the Reply button. "Don Guillett" wrote: try this =SUM(INDIRECT("A4:a"&ROW()-1)) -- Don Guillett SalesAid Software "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
I tried entering numbers in E5:E7 with SUM(E5:E7) in cell E8. With E8
selected, I inserted a row and put a number in the now empty cell E8. Excel automatically changed the SUM formula (now in E9) to SUM(E5:E8). Is this similar to the functionality you're looking for? "brent" wrote: No offense, guys, but do you think anyone who can write those functions (which yes, I can) is going to forget to modify the sum function when a new row is added? I'm looking for a relatively simple solution which can be applied by people who are generally going to be less knowledgable than those on this forum. (I'm sitting at a client corporation right now fixing spreadsheets that have this problem, as well as some of the ugly but traditional workarounds such as blank lines that create their own sets of issues.) Cheers. ps please nobody be offended by the Disagree buttons that I clicked. I thought that would bring up a comment section -- it took a while to spot the Reply button. "Don Guillett" wrote: try this =SUM(INDIRECT("A4:a"&ROW()-1)) -- Don Guillett SalesAid Software "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
Easy to adapt:
=SUM(D1:INDEX(D:D,ROW()-1)) which is better than having INDIRECT or even OFFSET. Don Guillett wrote: not automatic on row insertion |
New Excel function SUMABOVE
well forgive us for not being able to appreciate that which we are not told.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "brent" wrote in message ... No offense, guys, but do you think anyone who can write those functions (which yes, I can) is going to forget to modify the sum function when a new row is added? I'm looking for a relatively simple solution which can be applied by people who are generally going to be less knowledgable than those on this forum. (I'm sitting at a client corporation right now fixing spreadsheets that have this problem, as well as some of the ugly but traditional workarounds such as blank lines that create their own sets of issues.) Cheers. ps please nobody be offended by the Disagree buttons that I clicked. I thought that would bring up a comment section -- it took a while to spot the Reply button. "Don Guillett" wrote: try this =SUM(INDIRECT("A4:a"&ROW()-1)) -- Don Guillett SalesAid Software "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
telepathy not working today? <g
"Bob Phillips" wrote: well forgive us for not being able to appreciate that which we are not told. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "brent" wrote in message ... No offense, guys, but do you think anyone who can write those functions (which yes, I can) is going to forget to modify the sum function when a new row is added? I'm looking for a relatively simple solution which can be applied by people who are generally going to be less knowledgable than those on this forum. (I'm sitting at a client corporation right now fixing spreadsheets that have this problem, as well as some of the ugly but traditional workarounds such as blank lines that create their own sets of issues.) Cheers. ps please nobody be offended by the Disagree buttons that I clicked. I thought that would bring up a comment section -- it took a while to spot the Reply button. "Don Guillett" wrote: try this =SUM(INDIRECT("A4:a"&ROW()-1)) -- Don Guillett SalesAid Software "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
New Excel function SUMABOVE
OP never came back. Too bad he did see my =sa() for simple
-- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... well forgive us for not being able to appreciate that which we are not told. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "brent" wrote in message ... No offense, guys, but do you think anyone who can write those functions (which yes, I can) is going to forget to modify the sum function when a new row is added? I'm looking for a relatively simple solution which can be applied by people who are generally going to be less knowledgable than those on this forum. (I'm sitting at a client corporation right now fixing spreadsheets that have this problem, as well as some of the ugly but traditional workarounds such as blank lines that create their own sets of issues.) Cheers. ps please nobody be offended by the Disagree buttons that I clicked. I thought that would bring up a comment section -- it took a while to spot the Reply button. "Don Guillett" wrote: try this =SUM(INDIRECT("A4:a"&ROW()-1)) -- Don Guillett SalesAid Software "brent" wrote in message ... Suggested new Excel function: SUMABOVE(Ref) Ref = Single cell reference in the same column as the function. The function automatically adds all values from REF through the cell preceding the function [if D4 = SUMABOVE(D1), the formula sums D1:D3]. This eliminates issues of inserting rows immediately above the formula row and having to adjust the formula to include them. Can also work similar to SUBTOTAL function, where overlapping SUMABOVE functions do not duplicate the amounts added. Alternatively, REF can be in a different column and a block will be added, i.e., if D4 = SUMABOVE(A1), the result is SUM(A1:D3). Similarly: SUMLEFT, SUMRIGHT, SUMDOWN ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...et.f unctions |
| All times are GMT +1. The time now is 12:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com