1. L

    I’m trying to find a match from 2 columns against another 2 columns

    Hi I want to see if column A+B can find a match against columns D+E Any help would be appreciated Thanks
  2. 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...
  3. 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...
  4. 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
  5. 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
  6. 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...
  7. 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.
  8. 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...
  9. 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:
  10. Z


    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...
  11. 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? :(
  12. 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...
  13. 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?
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back