Results 1 to 10 of 10

20131203, 04:00 #1
 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$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE))))
Can anyone please help me? Thank you so much!!

20131203, 05:53 #2
 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. HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20131203, 11:47 #3
 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$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE)))),"",((VLOOKUP(B2,'Carp ark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$41)+(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$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE))))

20131203, 11:59 #4
 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$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$41)+(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$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$41)+(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$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,6,FALSE))),((VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,5,FALSE))*('User Input'!$B$41)+(VLOOKUP(B2,'Carpark Rates'!$A$3:$G$9,4,FALSE)))))
Steve

20131203, 12:17 #5
 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!!

20131203, 13:06 #6
 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 ctrlshoftenter), to lookup the min that is >0:
=IFERROR(VLOOKUP(MIN(IF(A2:A4>0,A2:A4)),Table!A2:B 4,2,FALSE),"")
Steve

20131207, 01:29 #7
 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$61)+(VLOOKUP(C2,'Carpark Rates'!$A$3:$F$9,5,FALSE))),((VLOOKUP(C2,'Carpark Rates'!$A$3:$F$9,4,FALSE))*('User Input'!$B$61)+(VLOOKUP(C2,'Carpark Rates'!$A$3:$F$9,3,FALSE)))),"")
Thanks guys!!!!

20131207, 06:17 #8
 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

20131207, 08:18 #9
 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

20131207, 20:59 #10
 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