#value

  1. T

    User Function returning #Value

    Hi All I am currently using the below function to show if a row is hidden or not, when i first use the function it works perfectly but as soon as i unhide/hide a row all the values change to #VALUE . What am i doing wrong? Public Function isvisible(rng As Range) isvisible = Not...
  2. T

    Not seeing the syntax error

    I have the following formula that is giving me a #VALUE ! error and I've been looking at it so long I can't see where the syntax is wrong. Can someone please help? =IF(AND(Y17="Replacement2",Z8<3),"Wave "&'Project Data Sheet'!J73&" Print Order","Wave "&'Project Data Sheet'!B101&" Print...
  3. A

    #VALUE error for COUNTIFS formula

    I am getting #VALUE error the below COUNTIFS formula, tried various possible changes based on research, but nothing works =COUNTIFS($G$2:$G$2184,02.11.2019,$I$1:$I$2184,Resolved) Both conditions work when used independently
  4. H

    Date Calculations not working

    Hi +$d$8 (where the date is in a date formated field) +(g17x7) does not work. G17 is weeks post award. 7 (days of the week) all I keep getting is value. I tried in a different cell +$d$8+14 and that failed too. Anyone know why? I get the usual hopeless #value MS message. Thanks
  5. leopardhawk

    Need help with #VALUE! error

    Hello forum friends, Can someone please tell me why this formula is giving me the #VALUE ! error? "&TEXT(D36/(tax_tables!C39),"0%")&" The value in D36 is $1227.32 and the value in tax_tables!C39 is $1154.58. I want to display the result as a percentage. The formula is a smaller part of a...
  6. W

    search function with or function

    how do you incorporate the search & or function into the same formula? i keep getting #Value with my formula. Any help is appreciated. =if(search(or("Best","Good","Average"),A1,1)=1,"Pass","Fail") Thanks.
  7. D

    Getting #VALUE when using IF(OR())

    Hi, I'm trying to return the value of a couple cells, depending upon whether or not they contain a value. I'm using the formula below: =IF(OR(IF(NOT(ISBLANK(SEARCH($BP$2,B2))),$BP$2,""),IF(NOT(ISBLANK(SEARCH($BP$3,B2))),$BP$3,"")),"") The idea is that either BP2 or BP3 will contain a value...
  8. J

    IF formula

    Hi, I have this IF formula but give me error "#VALUE!" here is the code: =IF(AND(L4="AB/BC","QC"),K4/1.05,K4/1.13) here is the data:
  9. Z

    mode

    Trying to use mode on data with negatives times. <colgroup><col span="2"></colgroup><tbody> -0:11 #value ! -0:06 #value ! -0:03 #value ! -0:05 #value ! 0.01 0:08 0.00 0:00 0.00 0:01 The #value is removing the seconds if present formula INT(CJ15*1440)/1440 I'm removing seconds...
  10. C

    Formula identifies number - returns #value in the end - why?

    Hi, I have a long formula and have taken snapshots of the last steps in the evaluation chain: https://imgur.com/a/5z2NzW3 The formula identifies the value I am looking for but in the end it returns #value error. Can I please have some guidance on how to solve this issue? :(
  11. N

    FIND returning #VALUE! error

    The string in cell A1 is: Multi-Specialty - 12254 If I put this formula in B1: FIND("-", A1) It returns 6. But if I do: FIND(" - ",A1) I get a #VALUE ! error. Why can't it find that string of space-hyphen-space when FIND can normally find any string of characters? How would I do a...
  12. B

    dates arent working properly

    I have a number of dates in col A. If I copy the cell and paste value I get a number like 43566, which I'd expect. But if I write a formula in col B such as =datevalue(A1) I get a #VALUE ! error. Can anyone advise why this might be happening and if I can fix it?
  13. D

    EDate a VLookup when VLookup comes up blank

    Good Afternoon I am currently trying to add 12 months to a Vlookup formula result. The Vlookup formula references another workbook. The formula currently has an if statement to blank out the cell if the vlookup does not find a value. I am wanting to add 12 months to the vlookup. If the...
  14. M

    sumproduct help

    Excel 2007 Formula in S3: =SUMPRODUCT(-- (D3:D20,D23:D47,D51:D67,D70:D74,D77:D124,D129:D140,D143: D153,D156:D157=R3),P3:P20,P23:P47,P51:P67,P70:P74,P77:P12 4,P129:P140,P143:P153,P156:P157) Column D contains Amount in numericals. Column R3 is a Helper Cell containing name like KM in general...
  15. D

    #VALUE when saved in OneDrive

    I have a workbook that works perfectly when saved locally. Even with hidden data and "Protect Sheet" enabled, all is well. HOWEVER, when I save my workbook to OneDrive, and view the workbook online, all my functions return a #Value error. My worksheets have Review/Protect Sheet enabled...
  16. M

    Populating a Column Based Off of Two Columns Values

    Hi Everyone, I've posted a similar question, but am having new troubles. One column has 4 values, T12, T8, empty cells, and #VALUE !. The second column has values 1 through 4. The new column needs to take the value from the second column (numbers 1-4) and match it to a title. For example, if...
  17. B

    Struggle with MID Function

    Hello all, In column Y I have quote numbers. In column A I need to put the quote number from column Y if it begins with CP or DC and blank if not. I tried this but the result is not right and returning (#VALUE!) =IF(MID(Y12,1,2="DC"),Y12,IF(MID(Y12,1,2="CP"),Y12,"")) Not sure what I am...
  18. N

    Tracing the cell.Address or different cells in defined range which caused Type Mistmatch Error 13

    Hello Now knowing the cause of "Type Mistmatch Error 13" How can i know which cell or different cells which gave me Type Mismatch Error 13 column (Z) contained Formula value ie =X2*Y2 . By mistake the cell in Column(Y) was typed with something else rather implementing the formula eg in cell...
  19. B

    SUMIFS question

    Hi all, How do I modify part of the below formula ‘Doors’!T:T to reference just one cell ‘Doors’!C4 If I change this reference to a single cell I geta #VALUE ! Error rather than the single cell value. =SUMIFS('Doors'! T:T,'Doors'!$B:$B,'Buildings'!$B42)*(1+$E$2+$G$2) Steve
  20. 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?

Some videos you may like

This Week's Hot Topics

Top