formula to calculate cell values from previous cell calculation

**ash2019** · 09-05-2018, 05:14 AM

Hi guys

New to the forum, looking to increase my knowledge within Excel primarily focussed towards formulas

I am working on a spreadsheet where at the minute I am manually having to do a formula in each cell i wanted to know if there is a quicker efficient solution

I have values in two columns but I want to calculate the previous cell to the cell in the left and result to go in a new cell so for example

24 3892
39 3931
3 3934
7
15

so next cell would be = 3934 + 7 = 3941 and then the following would be 3941 + 15 = 3956 and so forth

At the minute i am doing this manually is there any way I can do this quickly by just dragging the formula down

**Pete_UK** · 09-05-2018, 05:18 AM

Assuming your data is in columns A and B, then you can put this formula in B2:

=IF(A2="","",A2+B1)

then copy this down.

Hope this helps.

Pete

**ash2019** · 09-05-2018, 05:28 AM

Originally Posted by Pete_UK

Assuming your data is in columns A and B, then you can put this formula in B2:

=IF(A2="","",A2+B1)

then copy this down.

Hope this helps.

Pete

Hi Pete

Thanks for your swift response

my columns are C and E but E is blank until I have done the formula

I have tried your formula but is is not generating a result

**ash2019** · 09-05-2018, 05:56 AM

i have got the formula working but when i drag the formula down it does not work

=IF(C4272="","",C4272+E4253)

if i delete E4253 and then reference the previous cell the correct result appears but does not work when i drag the formula down, any ideas?

E4253 is the cell where the previous value was calculated

**Pete_UK** · 09-05-2018, 06:21 AM

It would help if you attached a sample Excel workbook, as those cell references bear no relation to what you first posted.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

**ash2019** · 09-05-2018, 06:30 AM

Hi Pete

I have attached the spreadsheet

**Pete_UK** · 09-05-2018, 11:33 AM

You have a lot of #REF! errors towards the bottom of your sheet - perhaps you have deleted some rows. To avoid those, you can put this formula in D4:

=IF(OR(ISERROR(B4),ISERROR(C4)),"",IF(C4="H",B4+IF(COUNT(D$3:D3)=0,0,LOOKUP(9000000000,D$3:D3)),""))

then copy the formula down as far as you need to.

Hope this helps.

Pete

**popipipo** · 09-05-2018, 12:35 PM

=IF(A4<A5;ROW()-2;"")
I think if all your #REF! are recoverd this formula will work

**ash2019** · 09-05-2018, 02:52 PM

Originally Posted by Pete_UK

You have a lot of #REF! errors towards the bottom of your sheet - perhaps you have deleted some rows. To avoid those, you can put this formula in D4:

=IF(OR(ISERROR(B4),ISERROR(C4)),"",IF(C4="H",B4+IF(COUNT(D$3:D3)=0,0,LOOKUP(9000000000,D$3:D3)),""))

then copy the formula down as far as you need to.

Hope this helps.

Pete

Apologies Pete, those REF are due to me deleting rows to simplify the spreadsheet, so those are relevant

I have attached another spreadsheet which I hopes make sense, I want to copy the formula down but at the minute I am doing it one by one which is very time consuming!

Thanks

**ash2019** · 09-06-2018, 05:55 AM

Hi Pete,

I would appreciate your input, I have hundreds of rows where I am doing the formula manually your solution would be welcomed

**Pete_UK** · 09-06-2018, 06:01 AM

In your latest attachment, put this formula in B3:

=IF(A3="","",A3+B2)

then copy it down as far as you want.

Hope this helps.

Pete

**Glenn Kennedy** · 09-06-2018, 06:02 AM

maybe this in B2, copied down as far as needed:

=IF(A2="","",SUM($A$2:A2))

**ash2019** · 09-06-2018, 06:59 AM

Originally Posted by Pete_UK

In your latest attachment, put this formula in B3:

=IF(A3="","",A3+B2)

then copy it down as far as you want.

Hope this helps.

Pete

Hi Pete

That formula works perfectly in the example spreadsheet i provided

however when i do the same formula in the actual spreadsheet I am working on the calculations are incorrect

I think this is because I have filters in place on alot of rows which need to be in place, is there a solution to address this?

**Pete_UK** · 09-06-2018, 07:50 AM

If your real file is more like the one you posted in Post #6, then I have corrected the source of the #REF! errors and put this formula in D4:

=IF(C4="H",B4+IF(COUNT(D$3:D3)=0,0,LOOKUP(9000000000,D$3:D3)),"")

which has been copied down to the bottom of your data (and could be copied further if more data is to be added).

Is this what you are after?

Hope this helps.

Pete

**ash2019** · 09-06-2018, 02:49 PM

Originally Posted by Pete_UK

If your real file is more like the one you posted in Post #6, then I have corrected the source of the #REF! errors and put this formula in D4:

=IF(C4="H",B4+IF(COUNT(D$3:D3)=0,0,LOOKUP(9000000000,D$3:D3)),"")

which has been copied down to the bottom of your data (and could be copied further if more data is to be added).

Is this what you are after?

Hope this helps.

Pete

Hi Pete

Thats perfect, thanks for all your help!

I am trying to increase my knowledge within Excel, for my benefit keen to know what the formula is actually doing, could you break it down and explain?

Cheers!

**popipipo** · 09-06-2018, 03:36 PM

What is wrong with this simple formula:

Please Login or Register  to view this content.

**ash2019** · 09-07-2018, 07:23 AM

Originally Posted by popipipo

What is wrong with this simple formula:

Please Login or Register  to view this content.

I have tried this formula too but for some reason I keep getting an error message appear

**ash2019** · 09-07-2018, 07:29 AM

Originally Posted by ash2019

Hi Pete

Thats perfect, thanks for all your help!

I am trying to increase my knowledge within Excel, for my benefit keen to know what the formula is actually doing, could you break it down and explain?

Cheers!

Hi Pete

I have many tabs which have the same data but the H is located in different rows, the H represents the total occasions the date appears

so what I am having to do is adjust the first formula to reference it to where the H and total overall figure ends before the next count of the date begins appears then copy it to down to the remaining H occurrences, is there any way this can be avoided as it would be nice just to copy and paste the formula without having to adjust it each time for each sheet.

Also I have thousands of rows of data, is there a quick way to fill the column with the same formula to the bottom of where the data ends? currently I am using the drag method, i am using excel 2016

thank you once again

**popipipo** · 09-07-2018, 01:52 PM

I don't have any error

**protonLeah** · 09-07-2018, 03:40 PM

An alternative:
1. With no formulas in column B (just the header), select A1:B1 down to last row in A
2. Data menu
a) Advanced
b) Select: Copy to another location (choose, say, E1)
c) Check "Unique record only"
d) o.k.
3. In cell E2, enter:

Please Login or Register  to view this content.

and fill down.

formula to calculate cell values from previous cell calculation

LinkBack

Thread Tools

Rate This Thread

Display

formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Re: formula to calculate cell values from previous cell calculation

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculation based upon previous cell

[SOLVED] How to create formula based on the previous cell values in order

Formula based on more than 240 of previous cell values

[SOLVED] for range of cell = n, calculate sum of cell + n-1 previous cell

[SOLVED] Excel 2007 : Getting new values from a cell while keeping past cell values in previous cells

Macro to auto populate the destination cell based on the values of the previous cell

Calculate using previous cell with value greater than 0

Bookmarks

Bookmarks

Posting Permissions