# Excel - Should I use IF function

Microsoft Microsoft excel 2007 (pc)
April 25, 2010 at 06:38:05
Specs: Windows Vista
 Hi AllCan 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

#1
April 25, 2010 at 08:07:48
 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 B1There are now three formulas - 1. In cell B1 `=IF(B\$1<=A2,B\$1,A2)`2. In cell B6 `=IF(B\$1=A3,A3,IF(B\$1

Report •

#2
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. 10000 95 95 15 15 660 660 74 74 9156i.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 •

#3
April 25, 2010 at 10:15:05
 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 and 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

Report •

Related Solutions

#4
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 •

#5
April 25, 2010 at 12:46:48
 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

Report •

#6
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 •

#7
April 25, 2010 at 15:49:44
 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 A1If you enter this formula in cell B1: `=\$A\$1`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\$1Drag the formula in cell B1 down a row to B2 and the formula is still =\$A\$1Now replace the formula in cell B1 with: `=A1`Drag it to cell C1 and the formula is now =B1In 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 =A2Replace the formula in B1 with =\$A1Drag it to C1 and it stays as =\$A1but 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

Report •

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

Report •