Wednesday 7 September 2016

Troubleshooting:Can some describe in layman terms what this excel formula is attempting: Error

Can some describe in layman terms what this excel formula is attempting:

=-SUMPRODUCT(SUBTOTAL(3,OFFSET('Budget2011'!$T$16:$T$8753,ROW('Budget2011'!$T$16:$T$8753)-MIN(ROW('Budget2011'!$T$16:$T$8753)),0,1)),--('Budget2011'!$O$16:$O$8753=$B6),'Budget2011'!L$16:L$8753)
 
Much appreciated!
 
Joe

Keys to the Problem Can some describe in layman terms what this excel formula is attempting:

Download SmartPCFixer for Free Now

SUMPRODUCT multiplies the corresponding elements of each of its arguments and then sums the results.
For example SUMPRODUCT(1,2,3,4,5,6) is effectively SUM(1*3*5,2*4*6)=SUM(15,48)=63

SUMPRODUCT has 3 arguments in this case. 

The last one is a range of 8738 cells in column L of sheet Budget2011, which it treats like an array of 8738 values.

The second argument contains ('Budget2011'!$O$16:$O$8753=$B6) which is an array of 8738 values, TRUE when the cell in column O equals the value in B6 and FALSE when it does not.  When converted to a number, TRUE is treated as 1 and FALSE as 0. 

So -('Budget2011'!$O$16:$O$8753=$B6) is an array of 8738 values, -1 when the cell in column O equals the value in B6 and 0 when it does not.
And, by applying a second minus sign, --('Budget2011'!$O$16:$O$8753=$B6) is an array of 8738 values, 1 when the cell in column O equals the value in B6 and 0 when it does not.
So the result of multiplying together the last 2 arguments will be an array of 8738 values, the value from column L when the corresponding cell in column O equals the value in B6 and 0 when it does not.
If these were the only 2 arguments of SUMPRODUCT the result would be equivalent to
=SUMIF('Budget2011'!$O$16:$O$8753,$B6,'Budget2011'!$L$16:$L$8753)
Now we come to the first argument of SUMPRODUCT which is a bit more tricky:
SUBTOTAL(3,OFFSET('Budget2011'!$T$16:$T$8753,ROW('Budget2011'!$T$16:$T$8753)-MIN(ROW('Budget2011'!$T$16:$T$8753)),0,1))
SUBTOTAL(3,XXX) gives a count of the non-blank entries in range XXX.
In this case the range XXX is defined by an OFFSET(...) function.
OFFSET('Budget2011'!$T$16:$T$8753,N,0,1)
is a single cell, N rows below cell T16 on sheet Budget2011.
In your case N is not a single value but an array of 8738 values.
ROW('Budget2011'!$T$16:$T$8753) is an array of row numbers, from 16 to 8753.
MIN(ROW('Budget2011'!$T$16:$T$8753)) is the minimum of that array, so 16
So ROW('Budget2011'!$T$16:$T$8753)-MIN(ROW('Budget2011'!$T$16:$T$8753)) is an array running from 0 to 8737.
So, SUBTOTAL(3,range) is an array of 0s and 1s with 0 for cells in column T which have content and 1 for cells which are empty.
I am puzzled why the formula has been made so complicated.
If I have interpreted it correctly it could have been written more simply as
=-SUMPRODUCT(--('Budget2011'!$T$16:$T$8753<>""),--('Budget2011'!$O$16:$O$8753=$B6),'Budget2011'!L$16:L$8753)
ie sum the cells from column L from rows in which the column O cell equals B6 and the column T cell is not empty, and then negate the result.

Don't Download Malware

Where are you getting the download?

There are malicious people who download valid copies of a popular download, modify the file with malicious software, and then upload the file with the same name. Make sure you are downloading from the developer's web page or a reputable company.

Don't install download manager

Many sites suggest or require you to install an installer or a download manager before allowing you to download a program you may be interested in downloading. These tools almost always cause your computer more problems and may even have malware or other spyware. Avoid any site claiming anything must be installed first before you can continue with your download.

Avoid advertisements on download pages

To help make money and pay for the bandwidth costs of supplying free the software, the final download page may have ads. Watch out for anything that looks like advertisements on the download page. Many advertisers try to trick viewers into clicking an ad with phrases like "Download Now", "Start Download", or "Continue" and that ad may open a separate download.

Cancel or deny any automatic download

Some sites may automatically try start a download or give the appearance that something needs to be installed or updated before the site or video can be seen. Never accept or install anything from any site unless you know what is downloading.

Recommended Method to Fix the Problem: Can some describe in layman terms what this excel formula is attempting::

How to Fix Can some describe in layman terms what this excel formula is attempting: with SmartPCFixer?

1. You can Download SmartPCFixer here. Install it on your system. When you open it, it will perform a scan.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been fixed.


Related: How to Download Toshiba Satellite A205-S5833 Supervisor Password Utility v.1.48.0.8C r2 driver,How to Download Toshiba Satellite C675-S7104 Laptop Checkup v.2.0.13.11 driver,How Can I Update & Download Toshiba Satellite L655-S5117 Face Recognition v.3.1.3 driver,Method to Update & Download Toshiba Satellite L845D-SP4328KL Atheros LAN Driver v.2.1.0.5,How to Update & Download Toshiba Satellite R930 Network Device ID Registry Setting Tool v.3.0.32.4 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: How to Fix - Can you change a hyperlink to automatically have two different font and colours??,Fast Solution to Problem: Can't download programs - no space,How to Fix - Can't add printer to Windows 7 Home Premium laptop?,Solution to Problem: Can not find the download for IE for windows 8.1 on microsoft.com,How to Fix Problem - Can not open a CD in Windows 7?,Can I safely remove earlier versions on my pc,Can I rotate a graph so it shows on my page sideways?,Can I safely ignore Office file validation alert message?,Can I send a JPEG file in a PDF form ?,can I review when my computer has been accessed from outside my home?

No comments:

Post a Comment