Can someone tell me what cells this formula is summing?
=SUM(I76-E73:E76)
Thanks.
Excel Help - SUM Function
kyle on 19/12/2023 - 16:55
Comments
here are the cells numbers:
E73 2127.72
E74 346.14
E75 87.12
E76 168.24
I76 425.44And the formula cell J76 gives me (1027.46)
My favourite part of this thread is where you said the exact same answer that trustnoone did but you said it first, but OP is giving trustnoone the kudos anyway.
My second favourite part is when
uraharaChatGPT then says the exact same answer again but using more words, but OP implies that it's wrong.
Depends on how you read spackbace's answer. I personally read it as
(I76) - (E73:E76), which expands to:(I76) - (E73 + E74 + E75 + E76)trustnoone's answer reads to me as
(I76 - (E73:E76)), which expands to((I76 - E73) + (I76 - E74) + (I76 - E75) + (I76 - E76))If I'm correct, they are different answers!
spackbace's = -2303.78
trustnoone's = -1027.46I read spackbace's answer as-2303.78 and chatgpt giving the same answer based on the dot point steps.
If you are using Excel 365, it is evaluated explicitly as dynamic formula and you get trustnoone’s answer
If you are using Excel 201x or above, and didn’t use the ctrl-shift-enter to insert a pair of curly brackets { }, then you might get spackbace’s answer
https://www.ablebits.com/office-addins-blog/excel-dynamic-ar…
What version of Excel are you using kyle?
This is the perfect question for ChatGPT rather than ozbargain
I think it first subtracts I76 to each item in E73 to E76. Then sums the result?
oh man!!!! you're a genius! Ozbargain forum comes to the rescue. Thanks very much.
Username does not check out.
TIL
You can ask Bing AI for these things
The formula
=SUM(I76-E73:E76)is used to calculate the difference between the sum of the values in the rangeE73:E76and the value in cellI76.Here's how it works:
- The range
E73:E76is summed up to give a total value. - The value in cell
I76is then subtracted from the total value obtained in the previous step. - The result of the subtraction is the final output of the formula.
In other words, the formula calculates the difference between the total value in the range
E73:E76and the value in cellI76.I hope this helps! Let me know if you have any other questions. 😊
Source: Conversation with Bing, 12/19/2023
(1) Excel SUM function | Exceljet. https://exceljet.net/functions/sum-function.
(2) SUM function - Microsoft Support. https://support.microsoft.com/en-us/office/sum-function-043e….
(3) SUM Function in Excel – How to Use - Excel Trick. https://exceltrick.com/functions/sum-function-in-excel/.
(4) SUM Function - Formula, Examples, How to Use SUM. https://corporatefinanceinstitute.com/resources/excel/sum-fu….Think the correct answer is the one above yours.
The value in cell I76 is then subtracted from the total value obtained in the previous step.
That is WRONG.
- The range
Interesting, my J76=SUM(I76-E73:E76) returns 257.2. (Excel 2003)
(Excel 2003)
Now there's a commitment to hating the ribbon bar.
Now there's a commitment to hating the ribbon bar.
True. But is this compatibility issue?
Excel 365 returns: 1027.46
Excel 2016 returns: #Value!
Excel 2003 (and OpenOffice, WPS) return: 257.2It is, Microsoft introduced array support into formulas sometime in 2018, which is how it's calculating. It creates an array of values (the value in I76 subtracting each individual value from the others and sums them all). That simply didn't exist in the older versions.
Excel got a lot better at error reporting at some point, which is why Excel 2016 throws an error. 2003 looks like it's just doing I76-E76 because Excel 2003 is rubbish.
That is because it evaluated SUM(I76-E76), likely because the current syntax wasn’t supported back then
Woah did not realise I was walking into an advanced excel function meeting
I just be using the sum function in most basic form and maybe a bit of table formatting
I just be using the sum function in most basic form and maybe a bit of table formatting
Check out XLOOKUP
XLOOKUP is handy but don't use it on a large spreadsheet, it's as slow as molasses (unless you sort your data and use the binary search).
I tried to use it to lookup a value based on two columns in each table and once it was doing a few hundred calculations it started telling me excel couldn't calculate them all at once.
it's as slow as molasses
It's much faster that VLOOKUP based on some testing I did on large data sets when it was introduced a couple of years back.
@jv: I'll have to go have another play with it, this was only a couple of weeks ago. I might have found some edge case that caused issues. I replaced it with a vlookup and a new table and it was fine - granted an xlookup on the new table probably would have worked fine as well.
XLOOKUP is the most efficient lookup formula. It replaces VLOOKUP, INDEX MATCH and IFNA all in one.
I use XLOOKUP on data sets that are close to a million rows and there is no issue. If you are running into problems that means that is some other fundamental issue. And as an advance excel user with a lot of experiences working on large models, it is very rare to see excel totally break down unless it has a circular error. Worst case scenario is that it will keep calculating for a min or two. You can literally see the progress of the calc at the bottom right.
@KaTst3R: lol I went back and looked at the formula that was an issue, definitely user error. I was doing a lookup of 3 different columns - take value A and compare it to Column A in another sheet, value B and Column B, value C and Column C, then return the value in Column D of that second sheet that matches on all three.
The problem stems from how xlookup calculates that. I figured it would look at A, B and C and if all three are true return the value. It doesn't, it creates three arrays of whether every cell is true or false for each column then merges that into one big array. And because I'd selected the columns as A:A rather than a range of something like A1:A300, it calculated all three to the max number of rows in the sheet, over a million. Which I think would try create an array of up to a quintillion values per calculation.
So I got the error "Excel ran out of resources while attempting to calculate". I can't blame it. This is why I hate using formulas and much prefer get and transform, but it wasn't my spreadsheet.
@freefall101: Use SUMPRODUCT((Column A=A)x(Column B=B)x(Column C=C)x(Column D)). This will turn SUMPRODUCT into a logic binary calc and if all 3 columns matches the value you want, it will return value in Column D. It will show TRUE x TRUE x TRUE x Column D value. Just make sure all 4 columns are the same range (i.e. same number of rows you are trying to pick up). Try that.
Replace x with the multiplication sign.
@KaTst3R: Cheers, although I'm returning text and not a number. I could do an index version of it but I assume INDEX/SUMPRODUCT would be less efficient than XLOOKUP anyway.
And i just learned the evolution of Excel today
Brah google
Better written as
=I76-SUM(E73:E76)
for understandability, and compatibility with earlier versions of Excel and other spreadsheets (Office clones)
Nope. That will give you a very different answer…
A thread for all insomniacs
I learnt from this, thanks. (I'd have said the syntax breaks the rules of algebra, but I certainly didn't expect multiple different answers from different Excel versions.)
And Libre Calc v7.5.9.2 (I use it, it's free with a dynamic stream of update / maintenance) returned #Value! error.
There is some confusion and a few incomplete answers in this thread. The answer depends on what version of Excel is being used, and maybe which cell the formula is entered into and what kind of formula it is.
OP has effectively clarified that it is a normal formula in the current / a recent version of Excel. In this case it considers E73:E76 to be an array, so it converts I76 to an array of the same dimension, and:
=SUM(I76-E73:E76)
becomes:
=(I76-E73)+(I76-E74)+(I76-E75)+(I76-E76)In Excel before dynamic arrays etc, it converts E73:E76 to a single value and the specific value depends on the row that the formula is entered into:
• If the formula is entered into row 73, then E73:E76 evaluates to the value in E73.
• If the formula is entered into row 74, then E73:E76 evaluates to the value in E74.
• So on for rows 75 and 76.
• If the formula is entered into any other row, then E73:E76 evaluates to the #VALUE! error.
And:
=SUM(I76-E73:E76)
Becomes one of the following:
=I76-E73
=I76-E74
=I76-E75
=I76-E76
=I76-#VALUE! (which evaluates to #VALUE!)If, in new Excel, you include an "@" to change the formula to the following:
=SUM(I76-@E73:E76)
Then you get the behaviour of old Excel.If, in old Excel, you apply it as an array formula, then you get the behaviour of new Excel. At least that's what I recall - I haven't checked this last part.
I76 minus E73 to E76