Entering a formula to round a number by a certain criteria

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I have the following formula in cell V32. (VLOOKUP(V26,'Special
AssessmentTable-AccInv'A3:B112,2,FALSE)) What I am trying to do is to
write a fomula that will round the figure off that is looked up. The
criteria for the formula is if the number that is lookup is below a
$1,000 round to the nearest $25. Or if the figure that is lookup is
above a $1,000 round down to the nearest $100.

What I have ran into here is when I attempt to write the formula, I
make a circular reference to the cell that I am entering the formula
into. How do I write a formula to perform a function in the same
cell? Any suggestions?
 
Hi Don,

you have to use the IF formula with your result formula. e.g.
=IF(VLOOKUP(...)<1000,ROUND(VLOOKUP(....)/25,0)*25,
ROUND(VLOOKUP(....)/100,0)*100)

Frank
 
Don said:
I have the following formula in cell V32. (VLOOKUP(V26,'Special
AssessmentTable-AccInv'A3:B112,2,FALSE)) What I am trying to do is to
write a fomula that will round the figure off that is looked up. The
criteria for the formula is if the number that is lookup is below a
$1,000 round to the nearest $25. Or if the figure that is lookup is
above a $1,000 round down to the nearest $100.

What I have ran into here is when I attempt to write the formula, I
make a circular reference to the cell that I am entering the formula
into. How do I write a formula to perform a function in the same
cell? Any suggestions?
Click to expand...

If your formula (in V32) were doing the rounding you ask for a number in A1,
you would use this formula:
=IF(A1<1000,ROUND(A1/25,0)*25,ROUNDDOWN(A1,-2))

So you just replace each occurrence of A1 in this formula by your VLOOKUP:
=IF(VLOOKUP(V26,'Special
AssessmentTable-AccInv'A3:B112,2,FALSE)<1000,ROUND(VLOOKUP(V26,'Special
AssessmentTable-AccInv'A3:B112,2,FALSE)/25,0)*25,ROUNDDOWN(VLOOKUP(V26,'Spec
ial AssessmentTable-AccInv'A3:B112,2,FALSE),-2))
 
Why would you do that? There are a few times when using a reference to the
same cell can be useful like
if you are creating a time stamp. Then you will turn on iteration under
tools>options>calculations.
In your case however it is suicidal..

If you want to round this way use something like

=IF(VLOOKUP(lookup_value,Table,2,0)<1000,ROUND(VLOOKUP(lookup_value,Table,2,
0)/25,0)*25,ROUND(VLOOKUP(lookup_value,Table,2,0),-2))

AND avoid circular reference
 
Frank,

there is no need really for the division and multiplication with 100 when
you round to the nearest 100

=ROUND(value,-2)

would be enough
 
Back
Top