#value

I have big data in column A of (011 12345 01/01/2019). Then I want columb B to get the dates only. I used the datevalue formula (=datevalue(right(a1;10)).It works but not on several cells with #value !. Data on column A are the same even on cells which has #value !. How can I fix those #value !?

I have recently opened a User Defined Function I explored several months ago and have found that it does not want to return output in its current standing. After inserting the User Defined Function in a random cell on a worksheet and inputting data ranges and other values to specify the type of...

hi, Any one have the formula so if a cell > 0, then that cell -30 but if that cell is empty dont display anything? I know if I used =if(a1>0,a1,"") that works, but when I add the -30 it either displays -30 or #value can someone sort me out? KR Trevor3007

What i want the formula to do is take the calculated cumulative weight loss in kgs from column F and convert it to Stones and pounds in Column G. It correctly does the conversion BUT until there is an entry in column F. (ie. while column f is blank for future weeks) then it shows #value ! in...

HI, So this works =IFERROR(VLOOKUP($H$3:$H$300, Formulas!$B$2:$Z$288,18,0),"") If I add this +('Pay (2)'!AH2) Then in returns #Value . I just need to add that cell on another sheet =IFERROR(VLOOKUP($H$3:$H$300, Formulas!$B$2:$Z$288,18,0),"")+('Pay (2)'!AH2) I also tried something like...

HI this works, but if I am returning 0 right I get #value . I would like to return nothing if there is nothing if I can =ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!$D$2:$Z$300,5,0)+0,0)+ABS(IFERROR(VLOOKUP(B2,'Paychex (2)'!$D$2:$Z$300,7,0)+0,0)))*(S2)

Hello: IFERROR(INDEX($E$3:$E$39,MATCH(B50,$B$3:$B$39,0)),"")*-1 This formula above is returning #VALUE ! if value in B50, is not found in range $B$3:$B$39 When I remove *-1, formula is returning "" but I need to have *-1 in formula. Can someone adjust formula so it returns "" when *-1...

Greetings, I having issues with a formula the finds a name in a range. =IF(F10:F600="Bob",SUMPRODUCT((A10:A600="NOT SENT")*B10:B600),"") I keep getting #value error. Any help would be appreciated Thanks

I have a workbook with 2000+ lines and 111 columns. As a result, SUMPRODUCT takes forever. My formula on Summary tab is: =SUMPRODUCT((Source!$C$33:$C$2792=$A2)*(Source!$E$33:$E$2792=$C2)*(Source!$H$32:$DH$32=D$1)*Source!$H$33:$DH$2792) I tried SUMIFS but got a #VALUE error because arrays are...

A3=IF(FIND("-",A9),LEFT(A9,FIND("-",A9)-1),CONCATENATE(A9)) returning "#VALUE!" instead What I am trying to accomplish is that if I typed/pasted ABCDEFG1-23 into cell A9, cell A3 then will display ABCDEFG1 and if I typed/pasted ABCDEFG1234 cell A9 would then equal the whole text in a cell A3

Hi All, I'm assuming this is a formatting issue but everything I try doesn't seem to fix the #Value issue. My spreadsheet is setup like: Merged Cell: DZ5:EL5 = "Fiscal Year 2018" Cells above labelled: Jan, Feb, Mar, Etc. in sequential order. Formula in cell DZ1...

I have this formula that I'm working with and cannot find a way to correct the error. =IF((AND($C$7=T12:T45,$C$13=W11:W32)),"YES","NO") Thanks for any help

The formula below as well as other recommended methods yields the #VALUE ! error. see following note. Cells are formatted identical to the the formula that is noted to work. A 1/26/1875 B 7/21/1881 C #VALUE ! The formula the yields #VALUE ! is =ROUNDDOWN((E18-E19)/365.25,0) this...

Hi I have a number 416 aligned to the left side of cell A1 and there is a green rectangle in the cell so this number is formatted as a text. So far so good. when I do =A1+1 Then excel will return 417 as number (aligned to the right) If I had "hello" in A1 and then I do =A1+1, I will get...

=SUM(H2+K2+N2)-(R2:AF2) Hi I can get this to work. Sometimes the cell in N is blank so it will return #value . Plus I want to - the range (r2 to Af2) Any better way for this to work thanks

Unfortunately I can't figure out how to place a screenshot of my spreadsheet to show you what I'm doing, so I'll try to describe it (I tried Mr Excel HTML Maker, but I keep ending an error in the code when I use User Defined Selection). I recorded and edited a macro (below) that looks at the...

=Y3*Z3*1.3/100 Hello what is the way to add on that formula if I do not wanted to see #VALUE ! if there is no data on related cell with formula. Thanks

Hi Why do I keep getting a #VALUE Error when I6 is "". It does have a formula in the cell but no actual value =IF(OR(I6=""),"",(K6+M6)/I6)/24 Thanks for any help Stephen

I am using the following formula to count the number of occurences of a date so long as a value in a corresponding column is >0. =COUNTIFS(Records!$F$2:$F$40000,">=1992-1-1",Records!$F$2:$F$40000,"<1993-1-1",Records!$I$2:$I$40000,">0") This works fine, but I wish to expand the range which...

I am using Excel 2016 not 365. I have been using the following formula to find the largest ( max ) value from a range of cells which are within a particular year. =AGGREGATE(14,6,Records!$I$2:$K$40000/(YEAR(Records!$F$2:$F$40000)=1992),1) I now wish to SUM or COUNT the values from the same...