# Boards

## Excel help please!

I am absolutely dogshit at using this software but know many of you are kings of understanding so hoping you can help me out.....

I have a coloumn of numbers derived from a simple equation (A)

A B C

345

560

1200

1650

What I want is a way for B to automatically be populated from the numbers in A in a specific way:

Any number from <500 then B equals exactly 500

Any number between 500-1000 then B rounds up to exactly 1000

Any number between 1000 and 1500 then B rounds up to 1500

And so on in increments of 500.

Coloumn C would need to be an equal column so that 500 from B would equally 50 in C or 1000 in B would equal 100 in C or 1500 in B would equal 250 in C

Can this be done??

I have no idea even how to google what I'm asking

I'm at work with limited mobile reception so apologies for not checking thread for replies but thanks in advance for any advice. Will be back online around 4.

## In column B

=MROUND(A1,500)

In column C

=B1/10.

## good work Steved

## hang on, that rounds

doesn't he need a roundup to those multiples?

## Oh good point

Make it =MROUND(A1+250,500)

## Actually, make it (A1+249.5)

So if the number was 500 it wouldn't round up to 1000.

## IF STATEMENT SON

## There's no way to make an if statement that works indefintely with this

For every new multiple of 500 you'd need to extend it, it would be a massive mess.

## Yes, you're probably right

but I got the impression that the numbers wouldn't be more than about 1500.

I dunno. Your solution is good. We're both right. You're just more efficient. Like a nazi.

You nazi.

## Could also use an if statement

for example

=IF(B5500,1000,IF(B5>1000,1500,"")))

## Sorry, I pasted that wrong

=IF(B5500,1000,IF(B5>1000,1500,"")))

## couldn't go on indefinitely though

## Agreed, but I prefer if statements in a way

as you can tinker more and if looks more like proper hard work.

=ROUNDUP(A1/500,0)*500

## Genius!

MROUND confuses me too much.

## Yeah yeah you've all done the easy bit,

but none of you have tackled the tricky special case in the question:

" 1500 in B would equal 250 in C"

## Why would it?

I don't get the original question.

## Thanks for the replies

Will try them out tomorrow as didn't get a chance to look at the thread at work.

Also apologies as I don't think I explained the question very well so more detail below:

A is variable that is calculated using an equation from 2 other variables (say X and Y).

Depending on the values of X and Y, A will equal anything from about 250 upto a max of 2500.

Therefore when the user inputs X and Y into the equation which resukts in A I need the excel spreadsheet to be "smart" enough to round up A to the nearest 500 (column B).

There will not be multiple rows (sorry I think I explained this poorly before). Each X and Y input will be unique and then will not be saved ready for the next input. In essence I'm trying to create a calculator to help with dosing and preparing IV antibiotic infusions so would be used on an individual pt basis by nurses/Drs.

Not sure if that changes the advice from anyone?

## So you're only putting each formula in one cell then.

Phil's answer will work for column B and Steved's for column C.

## Yes one cell only

Will try this tomorrow - thanks again to all who helped

## NEW QUERY

Potholes: http://i.imgur.com/VGnD4RE.png

^This spreadsheet has three sets of data:

- Col A: a list of areas that could be any size, but which are to be banded into one of three categories

- Col B: a depth that is one of either 40, 60, or 100

- Rates (£), depending on both area and depth, as per the table

I wanna pick out the rate based on the area category AND the depth.

In theory, I reckon it can be done in one step using a gargantuan IF AND nested combo to get the area banding and compare it to the depth.

But we've got excel 2003, which only allows you to nest seven IFs. AND the nesting would be proper beastly.

So what I did was this:

Create reference labels for the area category and the depth (easily picked out and identified for each case in columns B and D with a dead simple IF).

The labels just so happen to be prime numbers. Which means that they can be multiplied to give a unique reference which can easily be related back to the corresponding rate using a lengthy but simple IF chain.

And that was that.

But I was lucky. Some of the rates are the same (so even though there are nine rates, there are only seven different rates). But that's not guaranteed to be the case. If there were nine different rates, I'd be back to square 1. And an upgrade from Excel 2003 isn't happening (so no infinite IF nesting getout option for me).

Any thoughts on how best to pick out a rate from a table of rates based on two columns of criteria, without resorting to using IF?

Great question. Thanks for reading. Here we go.

I dont have excel with me but off the top of my head

=index($g$6:$j$9,(match(a3,$g$6:$g$9,1),(match(c3,$g$6:$j$6,0))

no wait, think that would only work if you had 0,50,100,(some high number that will cover the highest in your dataset) in cells g6:g9.

## missed out some brackets

=index($g$6:$j$9,(match(a3,$g$6:$g$9,1)),(match(c3,$g$6:$j$6,0)))

## INDEX and MATCH, eh?

think i see what's happening there. ta. i'll try and look into that in a bit.

#publicsectorefficiencysavings

think it will work, index tells it to look in that reference table, first match formula tells it which row to look in (the 1 in that first match formula is quite important as its not looking for exact match but nearest within range), second match tells it which column to look in, like a combined v/hlookup.

## The tips worked perfect, cheers

However I know have another issue....

I have a column (C) which is the the total of column A x column B (C1 = A1 x B1)

What I need is for the result in column C to always be less than or equal to 1000 even AxB will give numbers bigger than 1000.

How can I do this in column C please?

Thanks

=if(A1xB1>1000,"1000",A1xB1)

=min((axb),1000))

## Thanks!

Love DiS