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]

Ok lets see how this works for you. You said you are calc'ing averages, so I modified my suggestion to that...

	A​	B​	C​	D​	E​	F​	G​
19​		time 1​	time 1​	time 2​	time 2​	switch​	value used​
20​		manual​	cumulative​	manual​	cumulative​	​	​
21​	rough bore	10	5	20	15		10
22​	finish bore	10	5	20	15	x	15

G21=AVERAGEIF($B$20:$E$20,IF(F21="x","manual","cumulative"),B21:E21)
copied down

 

[Mechixx]

i like the use of fixing the switch cell! thatll come in handy

and yea i was thinking more so that, say if the switch is on in row 10, to use all the manual time inputs, or if the switch is off, use all the cumulative time inputs

 

[FDibbins]

good stuff, so does that averageif() do what you wanted?

 

[Mechixx]

ahhh ok, yea this is what i was thinking! i see what you did there

 

[Mechixx]

i think ittl do exactly what i needed

 

[FDibbins]

Great, let me know how you make out please?

 

[Mechixx]

Great, let me know how you make out please?

i will for sure! although it may not be for awhile since im off work next week.
do you just want me to reply to this thread with how i make out? or a PM instead?

 

[FDibbins]

You can do either, but posy here too

 

[Mechixx]

You can do either, but posy here too

Alrighty, sorry for the long wait but I've finally done it!, The averageif was very useful, and even set up some conditional formatting so when i input the switch x it marks the cells I'm not using a red color.
Thanks again for all of your help, wouldn't have been able to do it otherwise! Cheers!

 

[FDibbins]

Awesome, happy to help