need how to help creating a formula

David44010

New Member
Joined
Jul 1, 2018
Messages
8
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
just a little addition, I have found various options else where, but none of them have been clear to a novice user
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top