Reduce a number by a constant factor

I’m creating a spreadsheet for our Sales team.

We’re comparing hydraulic fluids.

So I have one cell that has the number of hours (life of the oil), and I’m trying to have another cell divide this number by a certain number depending on the input.

For example,
-for every 18 degree increase in temperature above 140 degrees, the life of the oil is cut in half.

Cell 1 (operating temperature)
Cell 2 (number of hours/oil life) is the cell that will be changed by the input in temperature.


Thank you all in advance for your help!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

Since this is your first post I'll change it for you but please note for the future.

Would you give some specific examples please and are the 18 degrees increments discrete or should the calculations be proportionate

For instance If A1 = say 158 degrees and A2 is say 2000 hours what result do you expect? 1/2 of 2000 =1000

What about if A2 = 176, is the result 1/2 of 1000?

...etc.

Richard - you changed the title in the wrong post. Just to let you know I've changed the opening post for you.

How much of this is chemistry and how much is Excel?

If you understand the chemistry and the exact formula you want to use, then describe the equation/formula to us and we should be able to help you put that into Excel.

If this is more about the chemistry, I recall (and a quick internet search seems to verify) that these calculations are usually based on the Arrhenius equation that relates reaction rates and temperature. The overall process I would expect to use for something like this:

1) From your specification that oil life is cut in half (so reaction rate doubles?) over an 18 K temperature change (note that the Arrhenius equations wants absolute temperatures), I would expect to calculate an "activation energy" that corresponds to that rate of change in the reaction rate (this page has a sidebar that derives the equation: http://lessons.chemistnate.com/arrhenius-equation.html ).
2) With that activation energy and a "baseline" temperature, you should be able to estimate oil life at any temperature reasonably close to the baseline temperature.

Wikipedia for the Arrhenius equation: https://en.wikipedia.org/wiki/Arrhenius_equation

Does that help?

Does "nailed it" mean "you understand the issue" or "you have resolved the issue"?

Originally Posted by AliGW

Richard - you changed the title in the wrong post. Just to let you know I've changed the opening post for you.

Thanks Ali,

There's was a glitch somewhere - one I've noticed occasionally before.

Picking the Edit Post in the original to try to get at the Title box merely threw up a new Reply window. It wasn't possible to change the title in the original hence I did the next best thing.

Welcome to the forum.
I'm not 100% sure at what points in the temperature rise you want the oil life to reduce.

Originally Posted by WheelerDealer01

For example, for every 18 degree increase in temperature above 140 degrees, the life of the oil is cut in half. // Cell 1 (operating temperature) // Cell 2 (number of hours/oil life) is the cell that will be changed by the input in temperature.

This first post implies that at 140°C it is 2000hrs and reduces every 18° after that, so at 158°C it is 1000hrs, at 176°C 500hrs, etc. In other words 140-157 is 2000, 158-175 is 1000, etc.
If this is what you want, try this formula (original life in A2, temperature in A1):

Formula:

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

...are the 18 degrees increments discrete or should the calculations be proportionate. For instance If A1 = say 158 degrees and A2 is say 2000 hours what result do you expect? 1/2 of 2000 =1000. What about if A2 = 176, is the result 1/2 of 1000?

Originally Posted by WheelerDealer01

Any value between 140 and 158 will divide the value in A2 in 1/2. 159- 176 is another 1/2, and so on.

Your follow-up post, in response to Richard's post, doesn't agree exactly with what he says, but instead says that the reduction starts at 140. You also give a range of 19° for the first step (140-158) and 17° for the second step (159-176). I assume that what you meant for this is 140-157, then 158-175, 176-193, etc (18° each time).
If this is what you want, try this (as above, original life in A2, temperature in A1):

Formula:

Please Login or Register  to view this content.

Note that both of the formulae above give stepped reductions - so a sudden drop-off in oil life at each 18° (what Richard calls 'discrete'). If you want the oil life to gradually reduce (what Richard refers to as 'proportionate') then the exact formula needed will depend on the exact mathematical relationship between the two factors. As examples only, here are two formulae for one type of gradual reduction (one for the 'start at 140' option and one for the 'start at 159' option):

Formula:

Please Login or Register  to view this content.

Do any of those help?