Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modifying formula to remove #N/A error

    Dear All,

    I have this array formula. I need to make this formula to remove #N/A errors and return 0 instead of blank. I tried modifying to IFERROR or IF(ISERROR) but can't seems to get it right. There is always an error.

    =IF(WEEKDAY(TODAY(),2)>5,((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,7,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE))))

    Can anyone please help me? Thank you so much!!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jolene,

    The standard test would be like this: =IF(ISNA(A1>4),"",A1)
    isna.JPG
    Where you would replace both the A1>4 & A1 references with your entire formula!
    However, I don't use Array formulas much so I'm not sure it this would work as an array formula but it is worth a try. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried following the standard way - =IF(ISNA(A1>4),"",A1).

    However, it can only work if I remove the WEEKDAY(TODAY(),2)>5.

    =IF(ISNA(((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,7,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE)))),"",((VLOOKUP(B2,'Carp ark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE))))

    How should I add it in? Thanks for your help!

    **My original code: =IF(WEEKDAY(TODAY(),2)>5,((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,7,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE))))

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about: (id you have XL2007+
    =iferror(IF(WEEKDAY(TODAY(),2)>5,((VLOOKUP(B2,'Car park Rates'!$A$3:$G$9,7,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE)))),0)

    Otherwise:
    =if(isna(IF(WEEKDAY(TODAY(),2)>5,((VLOOKUP(B2,'Car park Rates'!$A$3:$G$9,7,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE))))),0,IF(WEEKDAY(TODAY() ,2)>5,((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,7,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$4-1)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE)))))

    Steve

  5. #5
    New Lounger
    Join Date
    Dec 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much! It works perfectly!

    However I just realized that I can't return a 0 as this is about the cheapest carpark rates. It will return a blank as there is no carpark associate with $0. Is there any way to ignore the 0 value?

    table3.JPG

    This is how my table looks like. And I have this on another sheet. The empty cell contains this code - =IFERROR(VLOOKUP(MIN(Table!A2:A4),Table!A2:B4,2,FA LSE),"")

    park.JPG

    It should return AMK Hub instead.

    Is there anywhere to modify the code in the price table or is there any way to change this code =IFERROR(VLOOKUP(MIN(Table!A2:A4),Table!A2:B4,2,FA LSE),"")
    such that it will ignore 0 and return AMK Hub in this example instead?

    Thanks so much for your help!!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A null (or any text) string in the original formula will be ignored in calculations, so the min will be the lowest value...

    But to fix that particular formula you asked about in the followup, you can use the array formula (confirm with ctrl-shoft-enter), to lookup the min that is >0:
    =IFERROR(VLOOKUP(MIN(IF(A2:A4>0,A2:A4)),Table!A2:B 4,2,FALSE),"")

    Steve

  7. #7
    New Lounger
    Join Date
    Dec 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,

    Forgotten to try it on a weekend and turns out that the formula will always return the FALSE part regardless if its a weekday or weekend. If I change this to WEEKDAY(TODAY(),2)>5 , the code will not work at all. Please help me!!! Was thinking that the problem may lies with the weekday(today()) part.

    =IFERROR(IF(WEEKDAY(TODAY(),2)<5,((VLOOKUP(C2,'Car park Rates'!$A$3:$F$9,6,FALSE))*('User Input'!$B$6-1)+(VLOOKUP(C2,'Carpark Rates'!$A$3:$F$9,5,FALSE))),((VLOOKUP(C2,'Carpark Rates'!$A$3:$F$9,4,FALSE))*('User Input'!$B$6-1)+(VLOOKUP(C2,'Carpark Rates'!$A$3:$F$9,3,FALSE)))),"")

    Thanks guys!!!!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    could you attach a sample workbook? It would be good to explain what values work and what values do not and what the correct answer is supposed to be.

    Steve

  9. #9
    New Lounger
    Join Date
    Dec 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    As attached is the work book. I've provide some explanation under the Charges sheet.
    Thanks guys for your help!!

    Sample.xlsx

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    TJ,

    Just for fun, here is an alternative approach using all VBA to show the possibilities you can explore. Based on your rate chart, it will automatically calculate the charges using the Mall, day of the week, and number of hours. This assumes that any time past an hour counts as an additional hour charged but is easily customizable. In this sample, I have provided a calendar control to quickly add the date but have disabled it unless you would like to know how to use it. No complicated formulas, no error statements, no fuss, no muss.

    Maud

    ParkingCharges3.png ParkingCharges5.png

    Code:
    Private Sub Worksheet_Activate()
    '-----------------------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Worksheets("Input")
    Set ws2 = Worksheets("Rates")
    Dim Mall As String
    Dim DayOfWeek As Variant
    Dim Hours As Single
    Dim wdFirstHr As Currency
    Dim wdAdditionalHr As Currency
    Dim weFirstHr As Currency
    Dim weAdditionalHr As Currency
    Dim FeeFirstHr As Currency
    Dim FeeAdditionalHr As Currency
    Dim Charge As Currency
    On Error GoTo errorhandler
    Mall = ws1.Range("B3")
    DayOfWeek = ws1.Range("B4")
    Hours = ws1.Range("B5")
    '-------------------------------------
    'GET RATES
    Select Case Mall
        Case "Mall 1"
            wdFirstHr = 0.4
            wdAdditionalHr = 0.4
            weFirstHr = 2.6
            weAdditionalHr = 0
        Case "Mall 2"
            wdFirstHr = 1.4
            wdAdditionalHr = 0.7
            weFirstHr = 2.5
            weAdditionalHr = 0.8
        Case "Mall 3"
            wdFirstHr = 2
            wdAdditionalHr = 1.1
            weFirstHr = 3.5
            weAdditionalHr = 1.3
        Case "Mall 4"
            wdFirstHr = 1.6
            wdAdditionalHr = 0.4
            weFirstHr = 2
            weAdditionalHr = 0.2
        Case "Mall 5"
            wdFirstHr = 1.4
            wdAdditionalHr = 0.7
            weFirstHr = 2.8
            weAdditionalHr = 1
        Case "Mall 6"
            wdFirstHr = 2.2
            wdAdditionalHr = 1.3
            weFirstHr = 2
            weAdditionalHr = 0.1
        Case Else:
            GoTo errorhandler
    End Select
    '-----------------------------------
    'DETERMINE WEEK DAY OR WEEK END
    DayOfWeek = Weekday(DayOfWeek)
    Select Case DayOfWeek
        Case 1, 7 'WEEKEND
            FeeFirstHr = weFirstHr
            FeeAdditionalHr = weAdditionalHr
        Case 2 To 6
            FeeFirstHr = wdFirstHr
            FeeAdditionalHr = wdAdditionalHr
    End Select
    '-----------------------------------
    'CALCULATE FEE BASED ON HOURS
    Charge = FeeFirstHr + (WorksheetFunction.RoundUp((Hours - 1), 0) * FeeAdditionalHr)
    Debug.Print WorksheetFunction.RoundUp((Hours - 1), 0)
    Range("B2") = Charge
    Range("C2") = Mall
    Range("D2") = DayOfWeek
    Range("E2") = FeeFirstHr
    Range("F2") = WorksheetFunction.RoundUp((Hours - 1), 0) * FeeAdditionalHr
    Exit Sub
    '-----------------------------------
    'HANDLE AN INCORRECT ENTRY
    errorhandler:
    MsgBox "Make sure you have correctly entered the Mall, Date, and Hours in the correct format then try again."
    Worksheets("Input").Activate
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •