need how to help creating a formula

[David44010]

basically what I want to accomplish is a formula that will let me do ( x+y = y )

I want to accomplish this in 2 to 3 cells max

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]variable title[/TD]
[TD]a changing vairable[/TD]
[TD]end total[/TD]
[/TR]
[TR]
[TD]item 1[/TD]
[TD]n[/TD]
[TD]current total +n[/TD]
[/TR]
[TR]
[TD]item 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

we'll use excel notation and call them Cell A-1,2,3 and Row A,B,C

so Cell B2 will be a changing variable, a single count per a small cycle, reset at the end of the cycle, (ex: a daily count).
and Cell B3 will be the total of the current count of Cell B2 + the previous balance of B3, reset on a larger cycle, ( weekly count)

so for example day one would have a count of 5 in cell B2 and B3 would also have a count of 5
however day 2 would have a count of 3 in Cell B2 but Cell B3 would list a total of 8 ( B2 + B3 + Previous balance of B3)
so each time Cell B2 would change, cell B3 would just keep increasing until it is reset.

how can I accomplish this in just 2 active Cells, or with a hidden formula or code string located else where?

any help or suggestions would be greatly appreciated

 

[David44010]

just a little addition, I have found various options else where, but none of them have been clear to a novice user

 

[Joyner]

Hello David, depending on exactly what you want to do, you may need to use VBA, but this works for me for the increasing amount, you do need another formula hidden somewhere, I just include it as C3.

So your input cell is B2

Results in B3 formula is:

=B2+C3


Formula at C3 is:

=B2+B3-B2

I'm not sure about the "reset" part though

 

[Joyner]

I didn't mention for this to work you may need to change your File; Options; Formulas to "Allow interative calcuation"; Maximum Iterations to 1

 

[Joyner]

Anyother calculation on the workbook will force the formula to recalculate andcontinually add to your sum, so this solution may not work for you and I thinkyou will need a VBA solution.

 

[David44010]

thanks for the feed back,as I am a novice with Excel, when you say VBA, is this something with in excel it's self and if so how exactly would I implement it.
basically what I am looking to do is track sales of an item, 1 item per row, with daily, weekly, monthly and yearly totals, with daily changes adding to the weekly, monthly and yearly totals until they are reset at the end of their individual cycles

 

[Marcelo Branco]

Hi David

I recommend a different setup

Create a sheet, let's call it LogSheet, where you enter all the tansactions
Something like this
LogSheet

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Week​

[/td][td]

Item​

[/td][td]

Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

Item 1​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1​

[/td][td]

Item 2​

[/td][td]

8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

2​

[/td][td]

Item 1​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2​

[/td][td]

Item 2​

[/td][td]

11​

[/td][/tr]
[/table]

In the Summary sheet try this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Item​

[/td][td]

Current Total​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Item 1​

[/td][td]

8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Item 2​

[/td][td]

19​

[/td][/tr]
[/table]

Formula in B2 copied down
=SUMIF(LogSheet!B:B,A2,LogSheet!C:C)

Doing so, you record all the transactions (maybe important in the future to analyse or consult) and always have the Current Total updated.

Hope this helps

M.

 

[David44010]

thank you for your time and effort in responding, however again that is more than I want to have to display, I don't want a running list even if it's on another page like I assume your log sheet would be, what I want is this, (I'll put 3 grids up to show progression):

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]daily sale[/TD]
[TD]total sales for week 1[/TD]
[TD]total sales for the Jan[/TD]
[TD]total sales for the year[/TD]
[/TR]
[TR]
[TD]paint[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]aspirin[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]ivy[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]daily sale[/TD]
[TD]total sales for week 2[/TD]
[TD]total sales for the jan[/TD]
[TD]total sales for the year[/TD]
[/TR]
[TR]
[TD]paint[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]aspirin[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]ivy[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]daily sale[/TD]
[TD]total sales for week 1[/TD]
[TD]total sales for the feb[/TD]
[TD]total sales for the year[/TD]
[/TR]
[TR]
[TD]paint[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]aspirin[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]ivy[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


so basically all I want is a running total where the daily count is the only one that really changes, and when I put a new value in the daily sale count then the rest of the cells update to a total of current value of their cell plus the changed value of the daily cell, the weekly, monthly and yearly cycles can be done manually, however if there is anyone really creative or enthusiastic maybe you can create a formula that will automatically reset the count at the end of each cycle automatically.
but for right now I just want the ability to update a single cell with the current balance plus a new value - A + B + B = B, and the same formula can be used in each column as the total column will only be updated with it's value + the daily count:

weekly : B2 + C2 + C2 = C2
Monthly : B2 + D2 + D2 = D2
yearly : B2 + E2 + E2 = E2

it, to me, seems so simple, and when done manually is so easy, but trying to get the spread sheet to do is such a royal PITA!!!
also it has been mentioned here as well as other google finds that doing a coded formula would be best, so if anyone has that knowledge or ability or can direct me to a location that can teach me it would be great.

this kind of reminds me of one of my first computer classes where I had to make, (and I'll show my age here, lol), a DOS batch file menu to call 5 sub menu batch files, where everyone did just that, I was thinking " there's got to be a way to make it smaller", then I was able to get it down to 2 files, then after a little more digging and teacher pestering I was finally able to get it to a single file, and that's what this is like... getting it to it's smallest possible cell usage.

so to all that can help, thank you all for your time and efforts in this project.

 

[David44010]

I oops-ed on my formula examples there should only be one of the result cells:

should read:

weekly : B2 + C2 = C2
Monthly : B2 + D2 = D2
yearly : B2 + E2 = E2