Thread: Modifying formula to remove #N/A error

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

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

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

**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. 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. 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. 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. 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. 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. Hi,

As attached is the work book. I've provide some explanation under the Charges sheet.

Sample.xlsx

10. 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 weFirstHr As Currency
Dim FeeFirstHr 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
weFirstHr = 2.6
Case "Mall 2"
wdFirstHr = 1.4
weFirstHr = 2.5
Case "Mall 3"
wdFirstHr = 2
weFirstHr = 3.5
Case "Mall 4"
wdFirstHr = 1.6
weFirstHr = 2
Case "Mall 5"
wdFirstHr = 1.4
weFirstHr = 2.8
Case "Mall 6"
wdFirstHr = 2.2
weFirstHr = 2
Case Else:
GoTo errorhandler
End Select
'-----------------------------------
'DETERMINE WEEK DAY OR WEEK END
DayOfWeek = Weekday(DayOfWeek)
Select Case DayOfWeek
Case 1, 7 'WEEKEND
FeeFirstHr = weFirstHr
Case 2 To 6
FeeFirstHr = wdFirstHr
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```

Posting Permissions

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