# writing a formula in a cell to perform a function using the output from another formula

1. ## writing a formula in a cell to perform a function using the output from another formula

Sorry, I'm sorta new to excel other than simple data spreadsheets. I know how to simply sum/product multiple cells etc. However, I'm trying to make a product that performs multiple steps that give you a numeric outcome. One of those steps involves a substitution of a number for certain letters that are entered in a cell. Then I need to take the resulting number that is substituted for a letter and involve it in a simple sum with another cell. My question is how to do that? If I try to sum the cell with the substituted number in it it doesn't work because of the formula in the cell.

Thanks  Register To Reply

2. ## Re: writing a formula in a cell to perform a function using the output from another formul

See the yellow banner at the top of the page.  Register To Reply

3. ## Re: writing a formula in a cell to perform a function using the output from another formul  Register To Reply

4. ## Re: writing a formula in a cell to perform a function using the output from another formul

27 subtitute are not good solution! Also it returns "4" (text) not 4 (value)

Try another approach: ``Please Login or Register  to view this content.``
Then in A7:

=A2+A3+A5  Register To Reply

5. ## Re: writing a formula in a cell to perform a function using the output from another formul

OMG thank you! This works but I just realized that it creates another problem. I basically wrote the formula for A7 wrong. I need it to multiply actually, but can you do selective multiplication:

A5 * either A2 or A3 based on which one has an entry in it, ignoring one if its blank.

The function is to check a number that may contain a letter or a number in the first or second position. So the number being checked could be AE123 or 12345. In the case of a letter in the first or second position that's where the "substitution" or now "match" comes in in cell A2. If the first character is a number then A7 simply gets the product of A3 and A5.

I apologize if this is confusing and thanks for your help.  Register To Reply

6. ## Re: writing a formula in a cell to perform a function using the output from another formul

Couldn't you replace that match formula with SEARCH/FIND?  Register To Reply

7. ## Re: writing a formula in a cell to perform a function using the output from another formul

I'm trying to figure out how to do that. Is that something you write into the spreadsheet so that whenever that value is input it is changed to the desired value. Or is it a tool for one user to search, find and change a selected value per use?  Register To Reply

8. ## Re: writing a formula in a cell to perform a function using the output from another formul

The MATCH part of the formula bebo021999 posted essentially finds the position of the value in A1 in the array that's passed as the second argument.

Since all the values in that array are unique you could convert the array to a string and do the same thing using FIND or search.

The formula would look something like this - can't post the entire formula as the board security doesn't like the less than sign in the data, it thinks it's an HTML tag opening character.

SEARCH(A1, "AKU")

Obviously you would replace AKU with a string containing all the characters of interest in the correct order.  Register To Reply

9. ## Re: writing a formula in a cell to perform a function using the output from another formul

I've got some more studying to try to figure out how this match formula would work with what I'm attempting. It may provide another route to get to where I need to go. As of now everything works except being able to write something that gives me a selective product between one cell that has a constant number in it and one of two cells based on which one is used/has an entry put into it.  Register To Reply

10. ## Re: writing a formula in a cell to perform a function using the output from another formul

A5 * either A2 or A3 based on which one has an entry in it, ignoring one if its blank.
Assuming that either A2 or A3 will always be blank, try:
Formula:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply

11. ## Re: writing a formula in a cell to perform a function using the output from another formul

Wow! Thank you so much. This works! Problem solved, thank you for your help everyone who contributed.  Register To Reply

12. ## Re: writing a formula in a cell to perform a function using the output from another formul

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.  Register To Reply