# Drawing a value from a cell

1. ## Drawing a value from a cell

I have a spreadsheet I am using for structural design. In it I evaluate values for seismic loading and wind lateral loading on shear walls. In column D I have seismic loads (lb/ft), in column E I have wind loads (lb/ft). In column H I use a formula to calculate the load on a wall based on the Max of columns D and E. My formula is:

=IF(MAX(D11:E11)=D11,D11*C11*\$B\$9&" Seismic", E11*C11*\$B\$9&" Wind")

As you can see, I figured out a way to add text to the cell so when it does the calculation if column D results in a higher load then it adds the text "Seismic" to the cell and if column E results in the higher load it adds the text "Wind".

My issue now is that the actual value in this cell which is derived from the formula gets used by other cells in order to calculate other things. Upon adding that text now my other cells give the #VALUE! error. So my question is, is there a way in the subsequent cells to have a formula that draws the value from column H? Is there a way, for example, for an equation to "remove" the word and only see the value?

2. ## Re: Drawing a value from a cell

Use just this formula without the text

=IF(MAX(D11:E11)=D11,D11*C11*\$B\$9&" Seismic", E11*C11*\$B\$9

Then format the Cell (Ctrl+1) as #" Wind"

3. ## Re: Drawing a value from a cell

you get better help, if you add a small excel file, without confidential information.

4. ## Re: Drawing a value from a cell

You can also have the word "Seismic" and "Wind" calculate in another column to the right of the number. That way the number stays as a number that can be used in other calculations and you can still tell visually which was larger.

5. ## Re: Drawing a value from a cell

Thank you Ace. That gets me halfway there.

This is helpful, but it only works if my Wind (column E) gives me the higher value. If column D gives me the higher value it's still seeing that text and screwing me up.

If all else fails I'll use another column as nigel suggests.

6. ## Re: Drawing a value from a cell

I have attached an excerpt of the file if that helps.

Please note, that the formula in the related cells has changed from what I originally posted, though it provides me with roughly the same result. It's just more correct from a technical (engineering) standpoint. But the formula still gives me a max value and appends the aforementioned text depending on the value.

7. ## Re: Drawing a value from a cell

You would need an additional step of Conditional formatting (CF) further to the original cell format. In CF, use this rule

=MAX(D11:E11)=D11

Format as #" Seismic"

See attached as an example

8. ## Re: Drawing a value from a cell

Thanks Ace. I might go that route. Is there a way to copy that down the column though? If I format paint it applies it to the whole column, but the cells used in the formula condition remain the same. For example it starts in row 11, but if I copy down to say row 25 the formatting in row 25 is based on the cells in row 11. I want the cells to match the adjacent ones.

9. ## Re: Drawing a value from a cell

Use the \$ sign for absolute or relative referencing in the CF formulas as required. In your case, you would need relative referencing

Also, set up the CF whilst selecting all the cells initially, rather than copying and pasting formats or using the format painter.

10. ## Re: Drawing a value from a cell

Ace, Can you provide some further info? I can't mimic what you did with the CF. So if I have D11 to 25 and E11 to 25 and I want to use the formatting in H. Are you saying I select H11:H25 go to the CF tab and do "use a formula"? I don't know if I am doing something in the wrong order or what. I cannot duplicate your CF. If I actually look at your CF Rules it looks wrong, but it works perfectly.

There are currently 1 users browsing this thread. (0 members and 1 guests)