1. W

    DATEVALUE function

    I have a column of data which I thought were dates but the data doesn't seem to behave as such. I have attempted to use Datavalue function but no am getting #value ! I believe I have checked my system date and time settings, Doesn't anyone have any advice?
  2. T

    SUMIFS Function Is Performing Inconsistently

    This problem has been driving me nuts, because it should be simple, but it's not working. I have the following formula in a table called tblCurrentHoldings on the sheet CurrentHoldings (see screenshot: https://1drv.ms/u/s!ArArDJ7WmD62grkdhF-Y-3Epey_0_g?e=vkjFxq)...
  3. A

    Complicated formula giving "#value" and "You've entered too many arguments for this function"

    Hello, I am trying to figure out why Excel is giving me errors about the new formulas that I am creating. I have an original formula that works and gives me updates: =IF($B$3="(All)",CONCATENATE("Code ",$B$2," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1),CONCATENATE("Code...
  4. M

    Please help!! WEEKDAY function

    Gentleman, I know this is weird but the WEEKDAY function works fine till 12/5/2015, any day after this it returns #VALUE . i mean 12/5/2015 will return 6 but the next day 13/5/2015 will return #VALUE . Not sure what is happening. Any help?
  5. S

    =if formula giving a #value! error

    Hello I use this formula but if there is no data in the other column "D" if gives a #VALUE ! error. How can I get rid of this and leave the cell blank? =iferror? Thank you =IF(D15>7,D15-7,0)
  6. S

    #Value! in Month

    Hi, I think its a format issue, how to solve ? formula applied in B2 =DAY(A2) C2 =MONTH(A2) <colgroup><col width="64" style="width: 48pt;" span="4"> <tbody> Date DD MM YY 12/05/19 12 5 2019 #DCE6F1[/URL] , align: left"]15/05/19 #VALUE! #VALUE! #VALUE! </tbody>
  7. A

    Suproduct formula calculation error

    Hello, Is there anything add to below formula that will avoid the error? If there are letter at on the target cells(column B) it shows #VALUE! =SUMPRODUCT((E5:E84="L")*B5:B84) Thank you.
  8. D

    How to hide

    I have a spread sheet with several formulas when the formula is not being used the cell displays #value ... how can i hide this till a value is added?
  9. C

    Why is IF returning #VALUE!? Seem correct to me.

    Hi, Below is part of the formula. The error is in the beginning. When evaluating; first logic test= FALSE, instead of running the formula in the "FALSE" section, it returns #VALUE !. Why? I can not see anything wrong...
  10. G

    Dynamic average_range by column text in AVERAGEIFS formula

    Hi, I am trying to obtain an average from multiple criteria using AVERAGEIFS function. The issue is I want to be able to specify the average range in a dynamic way based on column header name. I used index/match to specify the column name using header name for AVERAGEIF and it works like a...
  11. W

    removing #DIV/0! & #VALUE!

    What do I need to do to this equation, so I don't get the #DIV/0! result? =IF(E43<=(N43/2),((A25/(ROUNDDOWN((N43/E43),0)))*B43),A25*B43) Same with #VALUE ! =IFERROR((D25/(ROUNDDOWN((O43/H43),0))),"")*B43 Thanks
  12. G

    Index Match 2nd Match Help

    I came up with this formula INDEX('TABLE INPUT'!$B$6:$AAZ$309,MATCH($A7,INDEX('TABLE INPUT'!$C$6:$AAZ$309,0,MATCH($E7,'TABLE INPUT'!$A$3:$AAZ$3,0)-7),0),MATCH($E7,'TABLE INPUT'!$H$3:$AAZ$3,0)) I know i need to use small or large but I tried and get a #value error message <tbody> -> 47A x...
  13. T

    Any way to get a SUMPRODUCT formula for non-contiguous cells?

    What i would like is Sumproduct of array1= A1,A3. Array2 = B1, B3. =SUMPRODUCT((A1,A3),(B1,B3)) gives #VALUE . If you try to use {}, SUMPRODUCT will only accept constant numbers, not cell references. When i type the formula with the (), the formula tip seems to suggest it is working. For...
  14. J

    #VALUE! Countifs

    im getting #Value ! when doing a countifs. anything i am missing. i am basically wanting it to count the totals for jan when Q5-Q29991 = "yes", sales AC = a value in a cell, sales R5 = "new" and the sale date (AB) is in january. i have this formula in another section for the sales execs and it...
  15. P

    Linked cells showing #value when source linked file is not open

    Hi, I'm encountering an unusual problem. I have cells in workbook A with links to cells in Workbook B. When both workbooks are open, everything is fine. But if I close workbook B, the linked cells in workbook A change to an #value error. If I reopen workbook B, the error goes away and the...
  16. I

    Sumproduct, Left and #Value!

    I'm trying to count how many times specific text (the first 4 characters in a cell) appear in a column. Each cell starts with a code e.g. RC30 and then will have varying text after. I've tried VBA code Worksheets("Sheet1").Range("D1").Value =...
  17. S

    Multiple UDF #VALUE! Error

    Hello, I have two UDF's defined in my workbook. One in Module 1 and the other in Module 2. The first UDF is used solely on Sheet1, and the second UDF is used solely on Sheet2. When I calculate the Sheet1 and navigate to Sheet2, every UDF on the Sheet2 has a #VALUE ! error. And when I...
  18. N

    #VALUE! Error of SUMPRODUCT if cell value is ""

    Hi, I have a table calculating times based on several criteria. I have a problem with calculation if one of the cells in the criteria range (B2:B1000) is "" then I get #VALUE! Error. The following is sample table (real table has more than 1000 rows) and in this case B7 is empty cell value...
  19. B

    trying to change dd/mm/yy to mm/dd/yy

    <tbody> Dates Text to Columns Text to Columns Text to Columns Search "/" 23/11/2018 23 11 2018 3 1/3/2019 1/1/1900 3 2019 <tbody> #VALUE! </tbody> 31/03/2019 31 3 2019 3 1/11/2018 1/1/1900 11 2018 #VALUE! 24/10/2018 24 10 2018 3 </tbody> The first...
  20. P

    getting #value! instead of total

    when i paste this i get #value! =SUM(IF('Shirt Database'!A2:A500,'Goal Keeper List'!B61,SUMIF('Shirt Database'!$F$2:$F$500,{"sub on","sub off","started","injured"},'Shirt Database'!$K$2:$K$500))) TIA

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...