Multiplying Letters and Numbers

**eagg** · 03-26-2011, 04:25 PM

Hi everyone, first time posting. Kind of a newbie at excel here, and I've gone through most of the forum and some of the internet in a search for this answer, and I apologize if it's been answered before, but I couldn't find it.

I'm trying to multiply a column of letters by a column of numbers and have the resulting sum product appear in one cell.

I'd like to keep the formating of the text and only have numbers greater than 0 contribute to the multiplication (showing up in the answer). The problem I'm working on is related to chemical reactions, and I've attached an example sheet.

I can't say I really know how to work with VBA, so a formula solution would be fantastic if there is a way to do it without VBA.

Any help would be incredibly appreciated! Thank you for your time.

Best.

**MarvinP** · 03-26-2011, 06:10 PM

Hi,

I have 2 possible answers on the attached. I need to study a little more and see how the good guru's do this to get more tools in my box.

There needs to be a way using Small and not counting zeros and Array Formulas that would give you what you want. It is just a tool I don't have at hand, yet!

**eagg** · 03-26-2011, 06:44 PM

Hi Marvin,

I've been working on it for a while now, and the best I could come up with was the attached.

There's got to be a way to do this without creating new columns, I just can't figure it out. The problem is that there are 300 or so of these "reactions" and creating new columns just doesn't seem reasonable.

Thanks for the reply, I'm gonna keep working on it. I appreciate it!

Best.

**eagg** · 03-26-2011, 06:48 PM

Oops, forgot to attach the new file. Here it is!

**eagg** · 03-26-2011, 07:18 PM

Originally Posted by MarvinP

Hi,

I have 2 possible answers on the attached. I need to study a little more and see how the good guru's do this to get more tools in my box.

There needs to be a way using Small and not counting zeros and Array Formulas that would give you what you want. It is just a tool I don't have at hand, yet!

I really like the helper column, I'm going to look into that further. I suppose if I had more than 4 compounds participating in the reaction, I'd have to add INDEX and MATCH for the number of participating compounds.

**Charlie_Howell** · 03-26-2011, 07:22 PM

try this and let us know if it works for you.

**eagg** · 03-26-2011, 07:41 PM

Originally Posted by Charlie_Howell

try this and let us know if it works for you.

Hi Charlie,

Thanks, it looks very nice. I suppose I can automate it by putting a formula under the check box for compound participation. The real spreadsheet that I have has 300 Compound Reaction Columns, which should give a list of 300 reactions. I'm going to work on trying to make it compatible, so it displays all 300 reactions in a reaction formula.

Best.

**Charlie_Howell** · 03-26-2011, 08:04 PM

sounds good! if you need help just give us a yelp...lol

**martindwilson** · 03-26-2011, 08:31 PM

another way

**Charlie_Howell** · 03-26-2011, 09:05 PM

very nice Martin...Me likey

**eagg** · 03-26-2011, 09:28 PM

Originally Posted by martindwilson

another way

This is great. Thanks for all the help guys! I really appreciate it!

Multiplying Letters and Numbers

LinkBack

Thread Tools

Rate This Thread

Display

Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Re: Multiplying Letters and Numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions