Excel - Should I use IF function

Microsoft Microsoft excel 2007 (pc)
April 25, 2010 at 06:38:05
Specs: Windows Vista
Hi All
Can anyone help with a formula?
I have got a little way with it but now I am stuck. I am using IF function but not sure if that is the correct one.
e.g. I have got an amount in A1 say 10000. I want to break it down into incremants in other cells.
So if A1 is less than or equal to 95 I want to put that amount in A2.
If A1 is more than 95 then A2 should show 95 then any amount more than 95 up to and including 110 should be shown in A3.
If A1 is more than 110 then A2 = 95, A3 110, A4 any amount that is left up to 770, A5 any amout left up to 844 and A6 whatever is left over - in this example it would be 9156.
No cell should show a negative amount. The lowest amount would be zero.

See More: Excel - Should I use IF function

Report •

April 25, 2010 at 08:07:48

Here is an approach that uses a column of cells containing your breakpoints, and formulas that use these breakpoints.
The advantage of this is that you can change one or more breakpoints without having to edit the formulas.

In column A, starting at cell A2 enter your breakpoints.
Enter your value in Cell B1
There are now three formulas -
1. In cell B1


2. In cell B6

3. A formula in cell B3 which is dragged down and extended into B4 and B5

With this approach you could easily add additional breakpoints and just extend the formula further down.
First drag B6 and extend it to B7, then drag B5 to extend it to B6 and add the new breakpoint in A6

Here is the result with 10000:

	A	B
1		10000
2	95	95
3	110	110
4	770	770
5	844	844
6		9156

and here it is with a different value:
	A	B
1		500
2	95	95
3	110	110
4	770	390
5	844	0
6		0

and with a value less than 95
	A	B
1		90
2	95	90
3	110	0
4	770	0
5	844	0
6		0

You can use formula auditing to step through the formulas to see what is going on.

I hope that I understood your requirements!


Report •

April 25, 2010 at 08:49:16
Thanks for your help. I take it the first formula should go in B2.
I got things a bit mixed up in my first description. Sorry about that. The breakpoints are different but the final figure should be 9156 if the start figure is 10000.

95 95
15 15
660 660
74 74
i.e. 10000 minus the sum of the breakpoint figures.
Sorry but when I cut and paste my example from the spreadsheet into here it does not retain the spacing format.

Report •

April 25, 2010 at 10:15:05

Yes, you are correct, the first formula should have been in B2.

If you use the formulas I suggested with a column of breakpoints, it will not matter what values you use.

However you now say that the final figure in row 6 is the initial value minus the sum of the values in rows 2 to 5, which is different.

In that case the formula in B6 needs to be


This is what I now get:

	A	B
1		10000
2	95	95
3	15	15
4	660	660
5	74	74
6		9156

BTW to line up data use the <pre> and </pre> tags that you can find above the reply box.

Then use the Preview button and edit, as required, in the box below the preview. To preview again, check the 'Check To Show Confirmation Page Again' box and click 'Confirm and see post'


Report •

Related Solutions

April 25, 2010 at 10:36:00
I have tried the new formula in B6 which works for some values but not everything. Could you check the other formulas again please?
If you enter various figures in B1 you will notice inconsistencies.

Report •

April 25, 2010 at 12:46:48

Try these three formulas:
The one in B3 is dragged down to extend it to B5





If there are still inconsistencies, please post a couple of examples (using the pre tags)


Report •

April 25, 2010 at 13:47:00
Well done. I can't find any problems with it at all.
Can you tell me what $ does in the formula?
If I want to use cells other than those used in this example, what would I need to change in the formula? I would know doubt have to include the $ sign in appropriate places.

Report •

April 25, 2010 at 15:49:44

The $ sign is used when you don't want that part of the address to change when you drag it.

Enter a number or text in cell A1
If you enter this formula in cell B1:

you will see the value from cell A1.
If you now drag the formula one column to cell C1, you still get the value from cell A1 and the formula in C1 has not changed - it is still =$A$1

Drag the formula in cell B1 down a row to B2 and the formula is still =$A$1

Now replace the formula in cell B1 with:


Drag it to cell C1 and the formula is now =B1
In other words moving the formula one column has changed the column part of the formula by one column.

The same goes for dragging it down one row - it becomes =A2

Replace the formula in B1 with =$A1
Drag it to C1 and it stays as =$A1
but drag it down a row and it becomes =$A2

So the $ sign acts to 'lock' the column or row part of the formula.
The 'lock' also works when a formula is copied and pasted to another cell.

Hope that helps.

Glad to hear that the formulas now work 'as intended' - it took me a while to get my head round what you wanted!


Report •

April 25, 2010 at 16:03:28
Thanks for everything.
It is very much appreciated.
I do have a go myself, in fact I have spent many hours on it today but with only very limited results.


Report •

Ask Question