0

My basic issue is, can I apply a Formula which use the Cell value as condition?

Now let me explain what I need.

I have a Spreadsheet having list of inventoried Aluminium extrusions. I want Counts to display, where some materials are inventoried by the Pieces, others by the Size(Foot).

I want to count all materials by the Pieces, and not worry about converting anything to linear feet.

To simplify what I am trying to do, Let's say I have set up like this.

Column A         Column B          Column C           Column D            Column E
Color/type       Length        Unit Of Measure      Pieces counted     Converted to LF

Red angle           20               LF                   31                64
Blue tube           16               LF                   10                160
Orange angle        20               EA                   62                1240
Black channel       24               LF                   43                1032
Purple angle        16               EA                   71                1136
Green tube          12               EA                   34                408

Column E contains Formula, which multiplies the value in Column D with Column B, resulting in the linear Footage on hand.

Conditional formatting applied in Column E and the Criteria is,

if the value in column C is "EA" or "LF", and the column D has values, so that only the proper unit of measure for each material's Count can be seen.

This makes the counting difficult though since, the cells for anything tracked by "EA" in column C are BLACK whether there is anything in them or not. So I'm losing the place, as I look from the screen to the page and back.

What I would ideally like to do, if possible, column E automatically multiply the value entered into column C by the number in column B if the value in column C is "LF", but display the number exactly as entered in column D if the corresponding value in column C is "EA".

Ultimately, my end result should display like so.

Column A         Column B          Column C           Column D            Column E
Color/type       Length        Unit Of Measure      Pieces counted     Final # to enter

Red angle           20               LF                   31                64
Blue tube           16               LF                   10                160
Orange angle        20               EA                   62                62
Black channel       24               LF                   43                1032
Purple angle        16               EA                   71                71
Green tube          12               EA                   34                34

I lost the way to do this, although it's probably simpler than I am making it. I have inspired from the site explains "Apply formulas conditionally in Excel, based on text contents of another cell".

My knowledge on Excel is completely self-taught by trial and error, which of course means I really only know just enough to get myself in trouble. Any help on this would be greatly appreciated! Thanks!

  • 4
    =IF(C2="LF";D2*B2;D2) ? – User552853 Jan 15 '18 at 7:10
  • 2
    You are not the only one who is self-taught in excel - RTFM worked for me starting with excel version 2 and the most useful reference? The Microsoft Excel Function Reference - which I still have and use... And the other comment solves the problem neatly. But one possible improvement could be : =IF(TRIM(C2)="LF",D2*B2,D2) DO NOTE the use of commas and semi-colons depending on your system settings... – Solar Mike Jan 15 '18 at 7:25
  • sometimes wildcard compare is necessary with all the utf junk. – strobelight Jan 15 '18 at 12:28
  • "What I would ideally like to do, if possible, column E automatically multiply the value entered into column C .. " Do mean column D here? – p._phidot_ Jan 8 at 6:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.