How can I have a button or similiar function to switch cell from one forumla to another and back?

[Mechixx]

Hey there everyone! I've come across something that I'm not sure how to handle actually and would like some input on it.

So my predicament is that i have a table set up that i input time of each tool in a machining program, and it then calculates the average and percentage and kicks out a graph based on that.
It works great however i have it set up for inputting time cumulatively, so then there's a column that then takes a time and subtracts it from the previous time to get the actual time in seconds that tool had taken. And id still like to have the option, but also have the option of just putting in the time individually in case i just time each tool by itself.
I know the easiest would be just to subtract the accumulative times myself and input them as individual times but i like having excel being able to break down the accumulative times for me.

So im wondering what the best way to handle this is? so that i could input times as either accumulative, or individually? And i want it so that i can just add/delete tools and times as needed for each operation i study.

I was thinking maybe a button that could switch from accumulative time mode, to individual time mode or something? but not sure how that would work, any input would be greatly appreciated!!

Here's what my table looks like for reference.

 

[FDibbins]

You show a pic with some charts and tables, but that doesn't really tell us what formulas etc you are using.

Based on what you said, assuming you have different cell/s for the manual entries (if not, you should have), then I would do this with an IF statement.

1. have a cell where you enter X
2. Have a cell that says something like...
=if(C2="x",B2,A2)
where C2 would contain the X (switch), B2 would be the manual entry and A2 would be the cum calc

 

[Mechixx]

ya sorry, i shouldve been more specific on what formulas im using

so in the time columns, thats my manual data entry, the average column is each average of the tools time, and the tool time coumn is where it breaks down each individual time by subtracting the average time from the average time of the tool before it, and then the total column divides the tool time by the total time

would it be easier if i make a table here in the post, and put what equations im using?

so what your saying is, have 2 manual entry columns, one for if i want to do individual times, and one column for cumulative time?

 

[FDibbins]

I thought the cumulative was a calc?

either way, yes, that is what Im saying, then have a cell where you enter something to "switch" from 1 to the other in a 3rd cell

 

[Mechixx]

nope the cumulative is me entering that in, because sometimes i use the lap counter on the stop watch and write down the times as they come, but other times, i just record each individual tool

can that switch be a button?, i have no idea how to use them but they seem intriguing.

 

[FDibbins]

Yes it could, but it would be far simpler to create, maintain and use with just an x in a cell. This is what Im thinking about...

	A​	B​	C​	D​
9​	manual	Cumulative	Switch	Value Used
10​	10​	20​		20​
11​	10​	20​	x​	10​

D10=IF(C10="x",A10,B10)
copied down

 

[Mechixx]

ahh ok, thats a cool way of doing it! ill see if i can implement that into what i have

 

[Mechixx]

so do i have to have a manual, cumulative, and switch column for every time that i have?
since i want to get an average i usually collect the time at least 3-5 times

	time 1 manual	time 1 cumulative	time 2 manual	time 2 cumulative	switch	value used
rough bore
finish bore

<tbody>
</tbody>

so in this case, could the switch be used to switch from using manual and cumulative in both time 1 and time 2 instances?
i.e if switch on, use time 1 and time 2 manual values?

 

[FDibbins]

Yes, you could set that up so that the X gets applied to a "range" of items....

	A​	B​	C​	D​
9​	manual	Cumulative	Switch	Value Used
10​	10​	20​	x	10​
11​	10​	20​	​	10​

D10=IF($C$10="x",A10,B10)
Note how I "fixed" C10 with $ so that the reference will not change when copied down

If you have different "sets" you need to apply this to, you would need to adjust the formula to each "set
so =IF($C$10="x",A10,B10)
could become...
=IF($C$15="x",A15,B15)
if your next "set" began in row 15

 

[FDibbins]

hmm reading again, I think I misread your question. Will look again