"Greater than but less than" formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a single formula that would do the following:

If A1 is equal to or less than 500, deliver B1*1, if A1 is equal to or less than 1000, but more than 500, deliver B1*2, if A1 is equal to or less than 1500 but more than 1000, deliver B1*3, and so on.

Can anyone help with this seemingly very confusing and possibly impossible formula?

Aman
 
Aman,

=Int(A1/500)*B1

Regards,
Kevin


Aman said:
I'm trying to create a single formula that would do the following:

If A1 is equal to or less than 500, deliver B1*1, if A1 is equal to or
Click to expand...
less than 1000, but more than 500, deliver B1*2, if A1 is equal to or less
than 1500 but more than 1000, deliver B1*3, and so on.
 
Whoops

=Int(A1/500 + 1)*B1

Kevin


Kevin Stecyk said:
Aman,

=Int(A1/500)*B1

Regards,
Kevin



less than 1000, but more than 500, deliver B1*2, if A1 is equal to or less
than 1500 but more than 1000, deliver B1*3, and so on. impossible
Click to expand...
 
It is 1 too high at 500, 1000, 1500, etc

a quick modification
=INT(A1/500+1)-(MOD(A1,500)=0)
 
Kevin,

You treat = incorrectly. This might be what is required

=INT(A1/500.01 + 1)*B1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Just a heads up - but
That gives the wrong answer for 500.001, 1000.001, as examples.
 
[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






--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top