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.

Hi, 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=IF(B$1<=A2,B$1,A2)

2. In cell B6=IF(B$1<A5,0,B$1-A5)

3. A formula in cell B3 which is dragged down and extended into B4 and B5=IF(B$1>=A3,A3,IF(B$1<A2,0,B$1-A2))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 A6Here 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 95A 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!

Regards

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.10000

95 95

15 15

660 660

74 74

9156

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.

Hi, 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

=IF(SUM(B$2:B5)>B$1,0,IF(B$1<A5,0,B$1-SUM(A$2:A5)))This is what I now get:

A B 1 10000 2 95 95 3 15 15 4 660 660 5 74 74 6 9156BTW 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'

Regards

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.

Hi, Try these three formulas:

The one in B3 is dragged down to extend it to B5B2:

=IF(B$1<=A2,B$1,A2)

B3:=IF(B$1>=SUM(A$2:A3),A3,IF(B$1-SUM(A$2:A2)>0,B$1-SUM(A$2:A2),0))

B6:=IF(SUM(B$2:B5)>=B$1,0,IF(B$1<A5,0,B$1-SUM(A$2:A5)))If there are still inconsistencies, please post a couple of examples (using the pre tags)

Regards

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.

Hi, 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:=$A$1you 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$1Drag 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:

=A1

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 =$A2So 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!

Regards

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.Regards

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History