Forum Discussion
Converting a letter into a dollar amount
I am trying to set a excel document that will allow me to convert an X in a cell into a dollar amount in another cell. I would like to place an X in a cell in a column so that I don't have to keep typing in different numbers for each column. Is there a formula that will allow this?
Example:
| $5 | $10 | $15 | $20 | Total |
| X | $5 | |||
| X | $20 | |||
| X | $15 |
For this solution I'm presuming the data is not tabled:
=MMULT(N(Tally="X"),TOCOL(Amount))Hi Hans.
Best wishes for the New Year.
You caught me out for a moment, in that I thought from the colours that you were using a Table. In which case the headers would be text and SUMIFS would return 0.
To get around this, I defined a helper range 'amountHeader' from the headers 'amount' using
= VALUE(REPLACE(amount,1,1,""))so a dynamic array formula would be
= BYROW(selection, LAMBDA(s, SUMIFS(amountHelper#, s, "x") ))Mind you my first thought had been to use XLOOUP
= BYROW(selection, LAMBDA(s, XLOOKUP("x", s, VALUE(REPLACE(amount,1,1,"")),"") ))though that would not give the same result if multiple "x"s appeared within a row.
Then the thought occurred to me that not everyone uses dynamic array formulas all the time (difficult to believe I know), so to insert the result into the source Table
= SUMIFS(amountHelper#, Table1[@] selection, "x")with an explicit intersection of the current row and the user selection of "x"s.
Amazing how much of a meal one can make of an apparently simple question!
PeterBartholomew1 wrote:
Amazing how much of a meal one can make of an apparently simple question!
Hi Peter,
That's right up your alley! 😁
Best wishes to you too!
- =IF(A1="X", your_dollar_amount, "")
This formula checks if cell A1 contains an "X". If it does, it will display the dollar amount you specify. If there's no "X", it will display nothing (""), leaving the cell blank.
Drag this formula down along Column B, and it will adapt for each row based on the "X" or empty cell in Column A.- Thank you.
