Tag Info

Hot answers tagged

3

This array formula works for me - =IF(AND(A1:A3>TODAY(),B1:B3="Yes"), "Yes", "No") Type it and hit ctrl+shft+enter - you will see curly brackets appear.


2

The problem you're having is that a calculation in Excel is meant to be continuously calculated but you're wanting to treat it as a one-time calculator. In this case, I would probably suggest just adding another column or use another cell somewhere else on the sheet to calculate your final result.


2

What your customer is trying to tell you is that however you're generating dates is ending up with a character string instead of a date value that Excel understands. If the text format of your date column is because you are typing periods into Excel when entering dates: STOP. Enter dates with slashes and Excel will recognize it as a date: Just hit Enter. ...


1

You're overthinking this:   (131-130) + (135-131) + (140-135) + ... = 131 - 130 + 135 - 131 + 140 - 135 + ... = -130 + 131 - 131 + 135 - 135 + 140 ... = -130 + (131-131) + (135-135) + ... = -130 + 0 + 0 + ... + 120 = 120-130 i.e., the sum of the differences between pairs of consecutive numbers is simply the difference between the last number ...


1

This method is overkill in this case, but you could also do this with an array formula: =AVERAGE((A2:A7)-(A1:A6)) To enter an array formula, type it in, then, with your cursor still in the formula editing field, press the combinationCtrl+Shift+Enter. The formula should now should show up as ={AVERAGE((A2:A7)-(A1:A6))} ...but you can't type those ...


1

It looks like on import of the text file, Excel removed the comma separator from your numbers greater than one thousand. So to Excel those look like the text representation of what you see as a number so 10 000.00 Excel sees as the characters "10" + " " + "000.00" Use the substitute function =NUMBERVALUE(SUBSTITUTE(L1," ","")) to remove the spaces and ...


1

This method worked on my computer. Close all office programs. Click windows button + R, then write regedit and enter. First backup this key HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel then delete it. Reopen excel.


1

I know this is old, but as a reference: Option Explicit Private Const Q As String = "'" Private Const ROOT As String = "E:\Test\" Private Const FLDR As String = "SubFolder" Private Const DASHBRD As String = "Db.xlsx" Public Sub updateAllFiles() Dim ws As Worksheet, cn As ADODB.Connection, rs As ADODB.Recordset, sql As String ...


1

Actually I realised this is very easy to do with the LOOKUP() function. In Sheet 2 Cell B1 I would put: =LOOKUP(A1,'Sheet 1'!D1:F1,'Sheet 1'!A1:C1)


1

I know of no way to do it directly without VBA. But if you select all sheets that need to be changed then changing it on one sheet will propagate the change to all sheets. (I don't select multiple sheets very often, but it's either Ctrl+click or Shift+click on the tabs. I think Ctrl+click selects individual sheets and Shift+click selects all sheets from the ...



Only top voted, non community-wiki answers of a minimum length are eligible