Search results

Did you try it like this? =TEXT(J4+D56-K4-D57,"0.00 ")&IF((J4+D56-K4-D57)>0,"debit","credit")

Hi, With the autofilter on, to count the "Y"s: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A4,ROW(A4:A100)-ROW(A4),,1))*(A4:A100="Y")) and to count the "N"s: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A4,ROW(A4:A100)-ROW(A4),,1))*(A4:A100="N")) ------ Incidentally, you can do this more easily...

Okay, it sounds like a file the msstkprp dll depends upon is missing. I'll have to google for that. In the meantime, what operating system do you have and is it 32 or 64 bit?

Hi Vijay, The relevant information on the link was quoted in my last post. If you are not a local administrator then you'll need to ask your IT department to register the dll for you. (Even if you are, perhaps you should ask them to do it because company policy might not allow you to...

According to this link on Mark Dagosta's site you need to register a dll called Msstkprp. Make sure you're running as a local administrator when you register it: http://markdagosta.com/tag/listview/

Hi beroth, Array formulas can be slow to calculate if: -they reference large ranges -you use a lot of them in your worksheet -they contain expensive functions So generally: -Avoid using whole column references such as A:A or B:B (whole columns aren't allowed in array formulas prior to...

Max: =INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0)) 2nd largest: =INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,0))

Unfortunately there isn't a MAXIF() function in the current versions of Excel. You can use an array formula which combines the MAX() and IF() functions to get the same result, or you can use a helper column. Suppose you have some data where you have a list of words in column A and you have a...

Hi SirJB, I agree that SUMPRODUCT() is not volatile. I also agree that I prefer to use *IFS() whenever possible instead of SUMPRODUCT() because of calculation overhead and simplicity. The irony is that it is actually possible to make the *IFS functions volatile if you misuse them (!!)...

Hi Gina, So the COUNTIFS() formula would be like: =COUNTIFS(A2:A10,"Criteria1",B2:B10,"Criteria2",C2:C10,"Criteria3") Say you put that formula in cell L2. Then your AVERAGEIFS() formula would be...

Hi Deb, I'm not quite following the suggestion of using an array constant.... =COUNTIFS($C2:$C7,0,$B$2:$B$7,{">=4","<=12"}) returns a 2 element array so you need a SUM() wrapper: =SUM(COUNTIFS(C2:C7,0,B2:B7,{">=4","<=12"})) But, when you...

Provided you are using Excel 2007 or later, I think you can do this with COUNTIFS() which is simpler than SUMPRODUCT(). =COUNTIFS(Telephony!H10:H145,"Analogue Phones",Telephony!C10:C145,"<>",Telephony!C10:C145,"<>Broken") So that formula says count...

If you can assume that you want to delete all error values (ie. #DIV/0! etc, not just #N/A) then you can use the Range.SpecialCells() method to get a reference to the cells containing formula errors and then delete them. Sub foo() Dim rngToCheck As Range, rngToDelete As Range...

You don't need the -- to coerce the boolean arrays because you're also multiplying them. So these formulae all do the same thing: {=SUM(LARGE((G7:G21="ME")*(H7:H21="High")*(I7:I21),{1,2,3}))} =SUMPRODUCT(LARGE((G7:G21="ME")*(H7:H21="High")*(I7:I21),{1,2,3}))...

Many congratulations! :)

Congratulations on this outstanding achievement. Looking forward to your next 5K!

The option I would choose: In a spare column, eg cell E2, put the formula =C2*D2 and then fill down the column. Then you can use =SUMIF(B:B,"Age 8",E:E) Alternatively you can use SUMPRODUCT: =SUMPRODUCT(--(B2:B100="Age 8"),C2:C100,D2:D100) But the SUMPRODUCT formula is...

You can concatenate the cell's value into the string: ActiveWorkbook.SaveAs _ Filename:="C:FolderSubfolder" & ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value & "file.xlsx", _ FileFormat:=xlOpenXMLWorkbook, _ CreateBackup:=False Note the FileFormat and the...

Hi, Make sure the second array is the same size as the first array. Then loop through the first array and use the CInt() function to convert each element into an Integer and assign it to the second array. Since your second array should be of type Integer, you need to make sure that the values...

Congratulations and well done. Now your target is to win the race to 5K!

Since the values come from a single column, you can transpose them to a 1-D array and then use the VBA.Strings.Join() method to create a string from them. Here's a crude outline: Sub foo() Dim sSql As String sSql = "SELECT * from ABCD.EFGH_IJK where LMN IN " &...

Good idea. IMO Excel 2010 is a much better version than Excel 2007. :)

Try changing your SaveAs line to this: ActiveWorkbook.SaveAs _ Filename:="F:FinanceAccountsAccounts2013SNAPSHOTS" & "SNAPSHOT " & DATETEXT & ".xlsm", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ Password:="", _ WriteResPassword:="", _...

Hi Carley, There are spaces in the folder name so: x = Shell("explorer /n,/e,""group.netglobalCroydonBroker2013= Broker Complaints""", 1)

The SmartArtNode class was introduced in Office 2010. If you're using a version of Excel earlier than that then you won't be able to use it.