Assigning different value to a specfic number for caculation

Question

First, I'm new here and I'm would be so genuinely grateful to any guidance or help with, what might be, a simple question.

I'd like to assign the value 1069 to 30. And then divide the number displayed in a different cell by 1069.

So when I type 30 into a cell e5, excel converts 30 to 1069 then divides 8000 (or whatever value) in cell e4 by 1069. I have about 5 cells i want to convert to different specific numbers for the division. So. 30 converts to 1069. 25 converts to 891, 20 to 713, 15 to 535, etc.

Ultimately, I'd like to also take the value of the above equations and convert the answer to time, hrs and minutes.

So 30 converts to 1069. 8000 divided by 1069 which equals 7.48. Then convert 7.48 to hours and minutes in a different cell. Which would be around 7.5 hrs.

Thank you.

Answer 1

This might be easiest if you create a lookup table where you record the conversion values. Even though this can be done inside the formula, it will be easier to maintain the lookup table if the conversion values change.

In the following screenshot, I entered the conversion data and turned that into a table using Ctrl+T. I renamed the table to MappingTable.

Now I can use a simple Vlookup to return the corresponding number for the value in E5, using this formula:

=E4/VLOOKUP(E5,MappingTable,2,FALSE)

To convert the decimal number into a time value, divide it by 24.

=E6/24

Format the result as time.

Looking at the conversion values: it looks like the conversion factor is very close to 35.66666, so maybe you could do away with the lookup and just use that for the calculation

=E4/(E5*35.66666)

Of course, you can do this in one formula

=(E4/(E5*35.66666))/24

Answer 2

I've been doing some math here and it seems that at 0 you start of with a value of 1, then with each increase of 5, you add 178 to the total, e.g.;

Therefor I think you can work your way around your problem using a relatively easy formula. Let's say your typed value sits in A1, the value to be devided sits in B1 and C1 is the cell where the calculation is done. Also D1 can be the cell where you then transform that outcome to a time-stamp:

The formula used in C1:

=B1/(1+(A1/5)*178)

The formula used in D1 (and with a proper numberformat for time):

=C1/24

---

A last note is that if you are only interested in a time-stamp, you could just as well skip C1 to D1 calculations and use the following in C1:

=(B1/(1+(A1/5)*178))/24