I am Trying to create a single formula that would do the following:
If A1 is less then or equal to 499, deliver B1*1, if A1 is equal to or less than 999 but more than and including 500, deliver B1*2, If A1 is equal to or less than 1499 but more than and including 1000, deliver B1*3, AND SO ON.....
I have read the Posts on this and they don't Work
=Int(A1/500)*B1 Dont work, It gives the wrong values for 500, 1000, 1500, etc
+Int(A1/500+1)-(MOD(A1,500)=0 Dont work, I cant get Access to except this formula. It wont save. It changes back to whatever was in the Control Source.
I am not an expert at this and the third response from what I can tell looks like an answer for Excel and not Access, Well I cant figure it out. Creating the Table which is the first part makes no since on how to do it.
That post was as follows:
[This followup was posted to microsoft.public.excel.worksheet.functions
with an email copy to =?Utf-8?B?QW1hbg==?=.
Please use the newsgroup for further discussion.]
Three possibilities, in addition to the option already discussed by
others, are below. None of the options requires the thresholds to be
equally spaced, nor that the return values be 1,2,3, etc.
1) Use the formula =B1*(IF(A1<=0,0,IF(A1<=500,1,IF(A1<=1000,2,3))))
This might be the easiest to understand but it has two limitations.
First, IF statements can be nested only 7 deep. There are ways around
the limitation, but they just complicate the matter further. Second,
the thresholds and the returned values are hidden in the formula and
not immediately obvious from looking at the worksheet.
2) Create a table such as:
F G
2 0 1
3 500 2
4 1000 3
And, use the formula
=B1*INDEX(G2:G4,MATCH(A1,F2:F4,1)-(A1=INDEX(F2:F4,MATCH(A1,F2:F4,1))))
The 2nd part -- following the -( part -- is an adjustment for the fact
that you want the conditions to be 'less than or equal to'. The
advantage of this formula is that it is easy to adjust for more
conditions. The downside is that it might be somewhat intimidating to
a novice.
3) Use the VBA user defined function (UDF) given below as
=TieredPricing(F2:G4,A1,TRUE). The advantage is that it makes the
worksheet more 'self documented' and it further separates the code from
the data. The downside is the need for a VBA module and having to deal
with the accompanying 'This file contains macros' warning.
Function TieredPricing(LookupTable As Range, LookupValue As Double, _
Optional IncludeBoundaryValue As Boolean = False)
Dim Rslt As Variant
On Error Resume Next
Rslt = Application.WorksheetFunction.Match( _
LookupValue, LookupTable.Columns(1), 1)
If Not IsEmpty(Rslt) Then
If IncludeBoundaryValue _
And LookupTable.Cells(Rslt, 1).Value = LookupValue Then
If Rslt = 1 Then _
TieredPricing = _
"Look up value outside of table values" _
Else _
TieredPricing _
= LookupTable.Cells(Rslt - 1, 2).Value
Else
TieredPricing = _
LookupTable.Cells(Rslt, 2).Value
End If
Else
TieredPricing = "Look up value outside of table values"
End If
End Function
None of this is working Please HELP
Joel
If A1 is less then or equal to 499, deliver B1*1, if A1 is equal to or less than 999 but more than and including 500, deliver B1*2, If A1 is equal to or less than 1499 but more than and including 1000, deliver B1*3, AND SO ON.....
I have read the Posts on this and they don't Work
=Int(A1/500)*B1 Dont work, It gives the wrong values for 500, 1000, 1500, etc
+Int(A1/500+1)-(MOD(A1,500)=0 Dont work, I cant get Access to except this formula. It wont save. It changes back to whatever was in the Control Source.
I am not an expert at this and the third response from what I can tell looks like an answer for Excel and not Access, Well I cant figure it out. Creating the Table which is the first part makes no since on how to do it.
That post was as follows:
[This followup was posted to microsoft.public.excel.worksheet.functions
with an email copy to =?Utf-8?B?QW1hbg==?=.
Please use the newsgroup for further discussion.]
Three possibilities, in addition to the option already discussed by
others, are below. None of the options requires the thresholds to be
equally spaced, nor that the return values be 1,2,3, etc.
1) Use the formula =B1*(IF(A1<=0,0,IF(A1<=500,1,IF(A1<=1000,2,3))))
This might be the easiest to understand but it has two limitations.
First, IF statements can be nested only 7 deep. There are ways around
the limitation, but they just complicate the matter further. Second,
the thresholds and the returned values are hidden in the formula and
not immediately obvious from looking at the worksheet.
2) Create a table such as:
F G
2 0 1
3 500 2
4 1000 3
And, use the formula
=B1*INDEX(G2:G4,MATCH(A1,F2:F4,1)-(A1=INDEX(F2:F4,MATCH(A1,F2:F4,1))))
The 2nd part -- following the -( part -- is an adjustment for the fact
that you want the conditions to be 'less than or equal to'. The
advantage of this formula is that it is easy to adjust for more
conditions. The downside is that it might be somewhat intimidating to
a novice.
3) Use the VBA user defined function (UDF) given below as
=TieredPricing(F2:G4,A1,TRUE). The advantage is that it makes the
worksheet more 'self documented' and it further separates the code from
the data. The downside is the need for a VBA module and having to deal
with the accompanying 'This file contains macros' warning.
Function TieredPricing(LookupTable As Range, LookupValue As Double, _
Optional IncludeBoundaryValue As Boolean = False)
Dim Rslt As Variant
On Error Resume Next
Rslt = Application.WorksheetFunction.Match( _
LookupValue, LookupTable.Columns(1), 1)
If Not IsEmpty(Rslt) Then
If IncludeBoundaryValue _
And LookupTable.Cells(Rslt, 1).Value = LookupValue Then
If Rslt = 1 Then _
TieredPricing = _
"Look up value outside of table values" _
Else _
TieredPricing _
= LookupTable.Cells(Rslt - 1, 2).Value
Else
TieredPricing = _
LookupTable.Cells(Rslt, 2).Value
End If
Else
TieredPricing = "Look up value outside of table values"
End If
End Function
None of this is working Please HELP
Joel