Dimensional Analysis in Excel

August 4, 2023 2:25 PM

Does anyone have any tips for how to organize an excel sheet for doing dimensional analysis without making mistakes?

I find doing calculations with lots of unit conversions to be very intuitive with pen and paper where it's easy to include units with each number and see that they cancel appropriately to get correct answer. With excel I find that I am more prone to making mistakes and generally feel less confident that I have done the calculation correctly. I'm sure there is a way of organizing things better that would help, but I haven't figured out what that is, and I'm wondering what works for other people.

posted by 12%juicepulp to Computers & Internet (3 answers total)

Best answer: For me, sometimes it's doing it on pen and paper. If I want to figure out something with units I need to track in Excel, then I do the calculation step by step in a single column of values, with the units labelled in the next column over (usually to the right, but could be either side).

For example (a util is an economic unit representing a measure of utility or happiness to a person; how much benefit (or cost, if negative) you get from making a choice):

      A                 B
1     #         utils per dollar
2  =1/A1        dollars per util
3     #         utils per minute
4    60         minutes per hour
5  =A3*A4       utils per hour
6  =A2*A5       dollars per hour
7
8  =(A3/A1)*60  dollars per hour

where the # get filled in with the actual values I want to use; the calculation in A8 should equal A6, but I can check it (and then use the A8 calculation in other places if I need).
posted by Superilla at 3:00 PM on August 4, 2023

I've never found a non-kludge way to do this, other than keeping independent rows or columns for units and/or separate areas for conversion factors between units, also with labeled units.

I often also check with pencil and paper, as above. Excel is not the best tool for this, compared to how something like EES (engineering equation solver) handles units where the unit is an intrinsic part of how the number is stored.
posted by AbelMelveny at 3:08 PM on August 4, 2023 [1 favorite]

This has other drawbacks, but I've used Excel (and Google sheets) to work through unit conversions.

I set up two rows. One row for the numerator of the units. One row the denominator of the units. It has helped me verify the unit canceling like on paper.

I also like naming the cells with units in them so the formulas in calculations can serve as a double-check.
posted by KevCed at 3:28 AM on August 5, 2023