1. P

    #Value! error on if/or statement

    I'm looking for cell R4 to enter a "1" if cell B4= either "PF/C", "SG/SF" or "PG/SF". Here is the formula I used. Keep getting the #Value ! error. I need this to be a 1 or 0 so that I can run this as part of solver. Thank you! =IF(OR(B4="PF/C",B4="SG/SF",B4="PG/SF"),1*O4) I then changed it to...
  2. Trevor3007


    hi, I inserted =IF(B16>0,B16*O10), but if B16 is blank i get '#value!' error , how can I resolve? FYI - cell 010 formatting = Currency (2 decimal places) KR Trevor3007
  3. BrianGGG

    New FILTER function cannot accept an array in the filter value?

    Hi. I have the following reference table: <tbody>#DAE7F5[/URL] "] A B 135 Item Quarter 136 a 2 137 b 3 138 c 3 139 d 1 140 e 4 141 f 1 142 g 4 143 h 2 144 i 3 145 j 1 146 k 1 147 l 1 148 m 3 </tbody> Summary My goal is to "pivot" by the quarter column to...
  4. J

    How to Rank a set of data with #n/a, #VALUE!, in data

    I need to rank a set of data in a UDF. For example, if the data set is: <colgroup><col></colgroup><tbody> 70 #VALUE ! 54 41 95 </tbody> then the command Application.Rank results is an error. Is there a way to ignore #VALUE ! or #N/A and still rank the rest of data? I need a VBA solution.
  5. D


    Hi Guys, =SUMIFS(INDEX(Service!$E$4:$AM$45,MATCH(pay_table[@[Employee Number]],Service!$A$4:$A$45,0),0),Service!$E$3:$AM$3,">="&Data!$C$2,Service!$E$3:$AM$3,"<="&Data!$D$2) This is my formula that works great, but i'm trying to add another parameter to the SUMIF. Which only adds values that...
  6. M

    If #value! In an array then substitute #value!?

    Consider the following table. <tbody> A B C 1 1 =$A$1:$A$5 2 2 3 =VALUE("THREE") 4 4 5 5 </tbody> If the range is taken from: $A$1:$A$5, and that range is represented as an array in B1 using the formula entered: =$A$1:$A$5; which is equal to the array: {1; 2...
  7. M

    If/is #value! Then [action]?

    Is there a way to write an IF formula or an IS formula that checks only for the error #VALUE! (and no other error like #REF!/#NA!...etc), and then executes an action? For example something like: IF( ( (A1)=(#VALUE!) ), ("#YES VALUE! ERROR"), ("NO #VALUE! ERROR) ) ? OR Something like...
  8. L

    countblank with range

    Hi I have 3 ranges like below. I named A1:A5 as myrange1, C1:C3 as myrange2, E1:E2 as myrange3. Then I highlight A1:A5, C1:C3, and E1:E2 (user CTRL) and then named them as "ALL" When I do = countblank(myrange1), I get 2 When I do =countblank(ALL), I got #value Why is that? Thank you so...
  9. B


    Hi, I'm trying to get a return in (H5) based on Column E multiplying by Column Q (100%, 130%, 150%). When I type the formulas individually they work, as IF(AND(E5>=O5,E5<p5,g5*q5) and="" so="" on="" for="" each="" of="" the="" other="" two="" returns.="" however,="" when="" i="" try="" to=""...
  10. S

    #VALUE! returned, but want cell to be blank if source cell is blank

    Good morning! I want the cell to remain blank if there is nothing in the cell that I am referencing. Instead, it returns the #VALUE ! to the cell. Here is my formula: =IF(P3="WAIVE",0,IF("EE Value",$R$130,IF(P3="EE Select",$P$130,IF(P3="ES Value",$R$131,IF(P3="EC Value",$R$132,IF(P3="EF...
  11. Sean15

    Need some help with OR function

    Hi: I want Excel to return 0 if value in A2 is equal to 1310 or 1425 or 6756, if not return value in F2. This formula is returning error #value. IF(OR(A2=1310, A2=1425, A2=6756,””),F2) Could you help please? Regards, Sean
  12. P


    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 !?
  13. S

    User Defined Function Revisited giving #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...
  14. Trevor3007

    if cell > 0

    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
  15. R

    Formula #VALUE! when blank cell.

    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...
  16. Z

    Formul ? #Value

    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...
  17. Z

    Formula Help #Value on ABS

    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)
  18. Sean15

    formula is returning #VALUE! instead of ""

    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...
  19. J

    Using IF Function where logical test is for range

    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
  20. adrienne0914

    SUMPRODUCT alternative

    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...

Some videos you may like

This Week's Hot Topics