Making a String equal to a Number
Posted by Jack on August 26, 2001 8:19 PM
I'm relatively new at excel and I can't for the life of me find this function. I want to bind a number to text, for example:
"Deliverable Goods" = $250
Now I want to be able to type in Deliverable Goods and have that string appear, but at the bottom of the screen I want to have a total of the Deliverable Goods on that page. So say I had 6 Deliverable Goods on that page, I want a Total= $1500.
Any help would be greatly appreciated, I'm about to pull out my hair's hair. I just can't find this function and I'm dead in the water without it (perhaps it doesn't exist but I'd find that extremely wierd).
In essance I want A to equal 5 (A=5) so then I can take the amount of "A"'s in a column (for example 7) and have it total 35 at the bottom of the page.
Posted by Gerry on August 26, 2001 9:24 PM
If you want the number of "A"'s in Column A multiplied by 5 :-
=COUNTIF(A:A,"A")*5
Posted by Gerry on August 26, 2001 9:33 PM
Also ...
If you often need to change the text to be totaled and/or the value to multiply by, you can put the text and value in different cells from the formula and then refer to those cells in the formula. For example, put A in B1, 5 in C1, and this formula in D1 :-
=COUNTIF(A:A,BI)*C1
Posted by Jack on August 26, 2001 9:37 PM
Ok, here's how I got it to work. Remember I'm not that great at excel so there is probably a much easier better way.
I made 3 hidden columns.
column 1, changed the string to a number =len(e5) in my case it equaled 10
column 2, true or false....=IF(10=F5,1350,300) so if f5 equaled 10 it would give me the value i wanted 1350, if not it would give me 300
column 3, another true false cause if there was no value it would give me pesky 0's/300's (if there was no string it len would give me a value of 300 cause 0 also doesn't equal 10 from column 2) =IF((F5=0),0,G5) that got rid of em. And I can total this hidden column.
TaDa, Badda boom.
Posted by Jack on August 27, 2001 1:34 AM
Re: Also ...
Hey, thanks, that's perfect. I appreciate the help, you saved me tons of extra work.
Thanks again,
Jack
