Excel formula increment round up

Dell PRECISION
July 8, 2009 at 08:22:22
Specs: win xp 5.1.2600, 3mhz/2 gig
Hi all.
In excel 2003 I need a formula to do the following.

Sorry for my description, I dont know how to put it better.
I will have 2 numbers, A and B.

number A needs to be rounded UP to match the nearest increment of number B. (I will have number A)

Number B starts at 1400 and increments up in 400.

For example. Number A = 7124
Number B must equal: 1400 + (400 in this case 15 times)

I hope this makes sense.

Thank you for any input.


See More: Excel formula increment round up

Report •


#1
July 8, 2009 at 10:38:56
I will have 2 numbers, A and B.
A & B were letters the last time I checked, not numbers.
I believe you mean Cell A and Cell B contain numbers.

Try this:
In cell A1 is your number: 7124

In cell B1 enter the formula:

=CEILING((A1-1400),400)+1400

Is this what your looking for?

MIKE

http://www.skeptic.com/


Report •

#2
July 8, 2009 at 10:44:12
A small modification, if Cell A1 is less than 1400 we make our Cell B1 equal at least 1400:

=IF(A1<1400,1400,CEILING((A1-1400),400)+1400)

MIKE

http://www.skeptic.com/


Report •

#3
July 8, 2009 at 11:29:30
Hi Mike. Number A and B were not for columns, just to refer to them as number 1 and number 2. I didnt want to use 1 or 2 as I thought it would confuse matters... I suppose I just made it difficult in the end!

Thank you for your reply. It has done what I needed it to.
I am familiar with excel but not good enough for this conditional formatting. Could you help out with one more thing?

I the number in the example '7124' will change and go higher. But the minimum can be 7300. Can you tell me how to do conditional formatting on that? Like make it say error or something?
Thanks again.


Report •

Related Solutions

#4
July 8, 2009 at 11:56:00
conditional formatting
This is a specific procedure in Excel.
On the Task Bar,
Format,
Conditional Formatting.

Is that what you need?

But the minimum can be 7300
How do you determine when it should be 1400 as opposed to 7300?
Is it specified somewhere?

The more detailed the explanation the easier this will be for both of us. :-)

MIKE

http://www.skeptic.com/


Report •

#5
July 8, 2009 at 12:15:35
Mike, lets use number A and B please.

Number A will have a minimum of 7300. (this number will be given to me say in cell A1.

Number B starts at 1400 and increments by 400 like you have done perfectly.


Report •

#6
July 8, 2009 at 12:41:55
lets use number A and B please.
A and B are NOT numbers they are letters.......

Number A will have a minimum of 7300
Why?
Why not 1400?
Why not 0?


What are you trying to accomplish............

Explain it in detail............

What is the purpose........

MIKE

http://www.skeptic.com/


Report •

#7
July 8, 2009 at 12:51:07
Jeez mike.. I know they arent numbers.. It was for REFERENCE.

My job is to build masts that hold railway electrification.
The masts are allocated in increments of 400.
The mast allocation starts like this, 1400mm then it goes up in increments of 400mm. I dont know why it is like this... probably because the people that make it need it like that.
The minimum value of height for this project is 7300mm.

So, I have a calculation that gives me a value of X (This is algebra). Now X is not a number, but it symbolises a number. (Similarly to what I was doing with A and B)
So the value X needs to be incremented to a value of 400 like you did perfectly earlier.
'=IF(A1<1400,1400,CEILING((A1-1400),400)+1400)'

Your formula points to CELL A1. I want CELL A1 to say error when it is less than 7300mm. This may not seem to matter to you, but this value will carry over to other formulas. If it gives an error, then I will know where the problem is and I can adjust other allocation accordingly.

Thanks!!!


Report •

#8
July 8, 2009 at 13:19:43
A cell can have either a formula or a number, not both.
If you try to reference cell A1 to change it's value, with a formula in B1, which uses the value in cell A1, then you have set up a circular reference and it won't work.

You could do something like:

In Cell B1 enter the formula:

=IF((CEILING((A1-1400),400)+1400)<7300,"Error",CEILING((A1-1400),400)+1400)

You do realize that any value less than or equal to 7000 will give you an error?

Do you have a max height you can not go over?

How do you have your spreadsheet set up?

MIKE

http://www.skeptic.com/


Report •

#9
July 9, 2009 at 05:20:25
Wow, you are good mate!!!
That works... It is brilliant. Really I have a lot to learn with Excel!

Thank you Mike.
Ciao


Report •

#10
July 9, 2009 at 07:12:15
Glad I could help.

MIKE

http://www.skeptic.com/


Report •


Ask Question