Excel subtraction formula

[Leco]

I have a feeling that this is really easy but I can't figure it out!
I have a budget in cell B2 with (money spent up to a quarter date) in cells C2, D2, E2 and F2. Balance left in G2
What I want is the formula for G2.
A simple formula would obviously be G2=B2-F2 but if it's only part the way through the year F2 wouldn't have anything in it so I'd want say B2-D2
Up to now I've been changing the formula at the end of each quarter, but is there another way?
Many thanks

I Leco

 

[bkpchs237]

Leco,

If you have the exact figures spent to date in the cells C2 thru F2 (i.e. you've spent $30,000 in Q1 and then $80,000 THROUGH Q2) then you could use the formula:
=$B$2-LARGE(C2:F2,1)

If you place each individual quarter's expenditure in cells C2 thru F2 (i.e. you've spent $30,000 in Q1 and then $50,000 in Q2 for a total spent of $80,000 thus far) then you could use the formula:
=$B$2-SUM(C2:F2)

Hope this helps.

 

[TonyJollans]

Hi Leco,

If you could be sure that, say, D2 was greater than C2 if it had a value you could use ..

=B2-MAX(C2:F2)

But the more general ..

=IF(F2="",IF(E2="",IF(D2="",IF(C2="",B2,B2-C2),B2-D2),B2-E2),B2-F2)

.. should work all the time. If you might have spaces rather than empty cells, you will need trim the values before checking them ..

=IF(TRIM(F2)="",IF(TRIM(E2)="",IF(TRIM(D2)="",IF(TRIM(C2)="",B2,B2-C2),B2-D2),B2-E2),B2-F2)

Enjoy,
Tony

 

[Leco]

I knew it would be something simple.
Thanks to both for your help.
I get #NUM errors using the LARGE function but thanks anyway, I'll no doubt find a use for that in another workbook.
I've used Tony's second formula, which works a treat, as it is likely that I will have empty cells.
Thanks once again.


I Leco

 

[KenWright]

Are the values in C2,D2,E2,F2 cumulative, ie if you spent a total of 50K in Q1 then cell C2 would have 50K, and if you spent a further total of 30K in Q2 would cell D2 then be 80K. If so then the following formula should do it:-

=B2-INDEX(C2:F2,,MAX((C2:F2<>&quot;&quot*{1,2,3,4}))

array entered CTRL+SHIFT+ENTER

Even if a later quarter's figures are lower than a preceding one it shouldn't matter as this would indicate a credit, and hence an increased budget balance.

But if the quarters are independent and not cumulative, then the following should do it:-

=B2-SUM(C2:F2)

Regards
Ken................

 

[Leco]

Ah I'd forgotten about increased budget balance! Thanks Ken. Would you please explain the formula as I'm not used to arrays (the cells in question are now in row 5 just so's I don't get confused)

I Leco

 

[KenWright]

OK.

=B2-INDEX(C2:F2,,MAX((C2:F2<>&quot;&quot*{1,2,3,4}))

B2 is obviously your budget, and what you are looking to take away from it is the latest set of cumulative data, which means whichever cell in the range C2:F2 has the latest data.

The index function allows you to specify a range, ie C2:F2, and then the next two arguments allow to to specify how many rows down and columns across you want to go in that range. Example, =INDEX(C2:F2,0,3) says take the range C2:F2, starting at C2 and go 0 rows down and 3 columns across.

All you need now is some way to tell Excel how many rows and columns you need to move. There is only 1 row in the data, so you don't need the first argument and can leave it blank, but you still need to work out how many columns across to move. This is where the MAX((C2:F2<>&quot;&quot*{1,2,3,4}) bit comes in.

This part of the formula is an array formula, and what it does is take each of the cells in the range C2:F2, determine whethere or not they are = &quot;&quot;, and this in itself will give you an array in memory of say TRUE,TRUE,TRUE,FALSE (Assumes 4th Quarter is empty). It then multiplies this array by the array constant {1,2,3,4), so that you get the following:-

={TRUE*1,TRUE*2,TRUE*3,FALSE*4}

Excel sees TRUE as 1 and FALSE as 0, therefore this equates to:-

={1,2,3,0}

The MAX bit simply gives you the result 3, and this is what is passed to the INDEX function to tell it to use value from the 3rd cell in the range C2:F2.

Regards
Ken..................

 

[Leco]

Thanks Ken, that makes things very clear and understandable.
However, what did you mean by &quot;array entered CTRL+SHIFT+ENTER&quot; ?

I Leco

 

[KenWright]

You can't just put that formula into a cell and hit enter as it will not work. You must array enter it, which means pressing CTRL+SHIFT+ENTER at the same time to enter it.

By the way. You could also have written the formula as per the following:-

=B2-INDEX(C2:F2,,MAX((C2:F2<>&quot;&quot*TRANSPOSE(ROW(INDIRECT(&quot;1:4&quot))))

The TRANSPOSE(ROW(INDIRECT(&quot;1:4&quot)) might look a lot longer and unnecessary, and in this case it is, but imagine if you had a 100 cells to look at. Using (&quot;1:100&quotis easier than typing out 1 to 100 in an array constant.


Array formulas can be very powerful, and can cut down on the amount of formulas you need to put into your spreadsheet considerably. They can also look like magic sometimes

The most common example of this type of formula is from the SUMPRODUCT function, which although it doesn't need to be entered as an array, will nevertheless perform an array function.

Assume you have a series of values in A1:A20, and another series of values in B1:B20. You might want to multiply each set of values on each row and then sum them all up. Normally you would use a 3rd column with the formula =A1*B1, and then in C21 you would use =SUM(C1:C21) to add them all up. The power of an arry allows you to do this all in one cell. In any cell, simply put =SUMPRODUCT((A1:A20)*(B1:B20)) and it will add them all up for you without the need for the other column.

You can also do the same with a formula that ypou need to array enter, eg:-

=SUM((A1:A20)*(B1:B20)) array entered.

Just for the hell of it, take a look at the following if it is of interest:-

The following formula exploits the fact that Excel interprets TRUE as 1 and FALSE as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=J1)*(B9:B20=J2)*(C9:C20))

This sets up an array that gives you something that looks like this (depending on the variables in J1 and J2 of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------
35

and the SUM bit just adds up all the end values of the products


If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20=&quot;A&quot)

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on whether or not the criteria has been met on that row, and this is the same as counting the number of records that meet your criteria. Imagine the above tables without Column C, and the last one would look like the following:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
-------
8

Hope I haven't bored you to sleep yet, only these really can be good fun when you get the hang of them.

Regards
Ken...................

 

[TonyJollans]

Hi Ken,

Excellent information on array formulas but I don't see their use in this case.

When does your formula produce a different result from the much simpler B2-MAX(C2:F2), which takes the maximum value directly rather than taking (if I read you right) the value from the cell which has the maximum value?

Also the reason I supplied my second formula was that cumulative-to-date figures do not necessarily increase. I might spend 50K in Q1 and get a rebate of 10K in Q2 so showing 40K. Whilst unlikely over complete quarters it might happen near the beginning of one.

Enjoy,
Tony

 

[KenWright]

Hi Tony. If as I think the OP is now confirming, the data in each of the Quarters is cumulative, and looked like this:-

Budget Q1 Q2 Q3 Q4 Balance
200 60 100 90 =B2-MAX(C2:F2)

The wrong answer would be returned, as it would give 100 as the answer instead of 110.

The array answer does NOT however, give the max value. It simply uses the max function in conjunction with the array constant to determine which of the 4 cells C2:F2 has the latest data in it, ie if E2 has data whilst F2 doesn't then E2 is the latest, regardless of whether it has the biggest number. The array simply does the same job as all the IFs in your second formula.

I'm assuming your first formula was not the one that gave the OP his answer as he said he used your second, and I simply wanted to lose all the IFs. The array lets me do that.

Regards
Ken.............

 

[TonyJollans]

Thanks Ken,

Of course, I should have read more carefully.

By the way, I'm not here

Enjoy,
tony

 

[Leco]

Thanks once again Ken (and Tony) I like to have as much info as possible in order to expand my knowledge.
I've found that the array formula only works with actual numbers (i.e. not cells that contain a SUM) but I'm o.k. with that.

By the way I'm a she not a he!

I Leco

 

[KenWright]

LOL - I've lost count of the times I've done that Tony.

Leco - Oooooops - I meant she, honest

It does work with SUM in the cells though. It will work with any formula in the cells. Will happily send you a working example if you like.

Regards
Ken..............

 

[KenWright]

I'm wrong about the formulas bit, so sincere apologies.

The following amendment will fix the problem as long as any formula in those cells returns a number, even if it is 0, just not &quot;&quot;, but assumes you do not start with a credit, or ever end a cumulative quarter in credit (Which hopefully is extremely unlikely).

=B2-INDEX(C2:F2,,MAX((C2:F2>0)*{1,2,3,4}))

Regards
Ken...............

 

[Leco]

Yep that works. Very many thanks for all your help.

I Leco

 

[KenWright]

My pleasure. By the way, after all that, I still probably wouldn't do it that way. I would probably prefer a single helper cell in which I would manually enter the Quarter that we were in, eg assuming the helper cell was M1:-

In cell G1 your formula would now be:-

=B2-OFFSET(B2,,M1)

Which if you put 3 in M1 for Quarter 3, would then take whatever value was in the cell that is offset 3 columns to the right of the Budget (ie Q3 data) away from the budget in B2. Then it doesn't matter what is in your cells, and there are pretty much no caveats. Also makes it real easy to tell at a glance what Quarter it really is.

Regards
Ken..............