Creating conditional expression using Dropdown

Hello, I am working on a spreadsheet in Calc, and need to calculate a field based on the option selected in a Dropdown.
The idea is to have a list of devices, their wattage, and calculate the Amperage based on their type.

I have created a Named Range called “Buss”
Each of these will have a voltage supplied on the column next to it,
A | B
“LiFePo4” | 13.8
“USB” | 5
and so on.

the table will have this layout (in csv here). I am using the “Buss” range for the options in the final column.

HAM Radio, 50, =Sum(B2*volts), LiFePo4

I want to make the equation conditional based on the option selected in the Buss field. I want the voltages for each buss to be editable, so more busses can be added, or voltages can be managed (changing the Lithium Battery size for example)

so something along the lines of
=SUM(nX*(SelectedBuss Voltage))

I guess I have two main issues here.

  1. How can I associate the voltage to the Buss name in the control.
  2. How can I calculate the Amperage based on the selected Buss

I suppose I could write it in VB (is that a thing with Libre?)
but I figured there should be an easier way to go about it…

Any advice greatly appreciated!


Show us this layout.
Give an example in the file. Why CSV? Use ODS. Your task does not require programming.

Enter all bus and voltage combinations in the table.

The CSV was just as a means to share the schema, the file it’s self is using the default ODS format.

Here is the table
AmperageCalculator.ods (14.3 KB)

had to edit, the Named Range was broken

At the moment, I am directly referencing the field that has the voltage in order to accomplish the calculation.
As I was saying, I would like it to be dynamic based on the option selected in the “Buss” column

Give an example on the sheet below (in your file), where everything is counted and presented as you need. Explain which fields are calculated and which should be supplied from the dropdown list. Explain the formula (with reference to the column titles): what and how to multiply/add.
And we’ll figure out how best to organize the data.

It’s possible.


AmperageCalculator2.ods (13.4 KB)

Do you want to see the peaks for the sum of all devices working at the same time on a given supply?

If so, look at the example sheet. I’m not an EE :slight_smile: but I chose to peak at a root 2 multiplier.

Read up on SUMIF if this is about what you want. Notice that eventually you would want to define your input (data) table as Named Ranges and work from those. Also, what you want might be available from a Pivot Table, but those have a pretty steep learning curve.

Oh, and I just used Data-Validity-Allow List and entered the source types in the list for the dropdowns.

AmperageCalculator2.ods (11.3 KB)

I definitely agree about calc peak off of root 2, I was being lazy in my first set of calculations. Thanks for catching that and adding the appropriate value!

I think that’s a good work around for what I was trying to do for now.
I also think you’re right about the Pivot table, that’s probably the only way to do the original idea.

Originally I was hoping to calculate the amperage per device based on the Buss, then I could total those. However totaling them per buss then totaling that would probably work just as well.

I am doing this to calculate how certain wire solutions would work out.
8ga vs 6ga and so on. By making this table I can evaluate various configurations of devices based on different power options in order to ensure that I am not overloading the cabling I have.