"Greater than but less than" formula

[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

 

[Kevin Stecyk]

Aman,

=Int(A1/500)*B1

Regards,
Kevin

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.

 

[Kevin Stecyk]

Whoops

=Int(A1/500 + 1)*B1

Kevin

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

 

[Tom Ogilvy]

It is 1 too high at 500, 1000, 1500, etc

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

Just a heads up - but
That gives the wrong answer for 500.001, 1000.001, as examples.

 

[Kevin Stecyk]

Tom and Bob,

Thank you for the corrections!

Regards,
Kevin

 

[Tushar Mehta]

[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

 

[joeldault]

Dear Kevin Stecyk
I am unable to get this to work, Please help