#value

  1. Z

    Formulas over several sheets and cells

    Hi, I am not sure if there is a solution to this. I have a Workbook where I capture information. I have added some formulas ease the process. I have a Dashboard Worksheet that summarize some data. The formula is [COUNTIFS('Incident Register'!$B$5:$B$2001,'Data Sheet'!A4,'Incident...
  2. Z

    Search for a value in a column, then modify all the cells contain this value by adding a number to the end

    I am fairly new to VBA. I need to create a macro that searches the entire column K for keywords " C Box", for any cell in this column that contains these keywords, I need to add a numerical value to the end of the text (this numerical is always in the same row as the cell that contains the...
  3. wpennajr

    Excel VBA function returning #VALUE

    HI all, I'm new to this forum so forgive me if my question has been answered before, but I searched the FAQ and I didn't find an answer. I'm creating an Excel function to perform a statistical test. Basically, the input is a range of cells containing my data set. Then I create an array with...
  4. J

    UDF evaluates after every change

    Hi, I have an issue, i though pretty simple one but cant handle it so i guess was wrong. I have a UDF that calculates average of exchange rates between 2 dates Option Explicit Public Function averageFromRange() As Double Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("Exchange...
  5. D

    Xlookup, If, etc formulas and the dreaded #Value! that comes with "" when pasting as values

    In xlookup you can define what your error response is ~~ =xlookup(a3,b:b,c:c,<whateveryouwantyourerrorresponsetobe>,option,option). Typically I use "" as my error response. However when you copy the cell and paste as value (the result being an error state equaling "") in another cell...
  6. 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
  7. 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...
  8. 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...
  9. 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
  10. 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
  11. 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...
  12. 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.
  13. 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...
  14. 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:
  15. 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...
  16. 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? :(
  17. 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...
  18. 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?
  19. 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...
  20. 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...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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
Top