Is there a function compiler?

[danjackson5]

Hi All,

Is anyone aware of a tool which enables me to put my functions in "Plain English" and outputs in an excel formula format?

For example, i'm trying to work out the correct formula for the following:

IF Cell AB1 DOESN'T Show NPW, SUM Cells R1,T1,V1,X1,Z1.
IF Cell AB1 DOES Show "NPW" AND Cell W1 DOESN'T show anything, Show Cell AD1.
IF Cell AB1 DOES Show "NPW" AND Cell W1 DOES Show Something, Show Cell AC1.



I've got the wording down for the formula but it's difficult to put it into an actual statement (Split across separate lines for easier reading):

Please Login or Register  to view this content.

I find the excel one built-in is ok but not great. What would be better is if I could feed this information into a user friendly tool and select the arguments and it generates the correct formula.

Does anything like that exist? Thanks

All The Best
Dan

[Harribone]

You can use the Insert Function to help you build formulas if needed.

Just above the column headings on your sheet is the formula bar and if you click on the 'fx' you can use that to assist with formulas but you won't be able to build your formula above in that one go.
Recommend building each bit and then put it altogether to create one formula if it helps you at all.

Looking at your requirement I would therefore do this -

=IF(AB1<>"NPW",SUM(R1,T1,V1,X1,Z1),NEXT STEP)

NEXT STEP: IF(AND(AB1="NPW",W1=""),AD1,LAST STEP)

LAST STEP: IF(AND(AB1="NPW",W1<>""),AC1,"")


Note in the last step I have assumed you want the output to be blank if none of your criteria are met. You may want to change "" to "Error" or other output to suit your needs.
You can now copy one into the other to build up you formula:

Last step into Next step
=IF(AB1<>"NPW",SUM(R1,T1,V1,X1,Z1),NEXT STEP)

NEXT STEP: IF(AND(AB1="NPW",W1=""),AD1,IF(AND(AB1="NPW",W1<>""),AC1,""))

Next step into the first part

=IF(AB1<>"NPW",SUM(R1,T1,V1,X1,Z1),IF(AND(AB1="NPW",W1=""),AD1,IF(AND(AB1="NPW",W1<>""),AC1,"")))

[danjackson5]

Hi Harribone,

Thanks. I can certainly see the logic in your approach. I'm not sure if it's a form of mild dyslexia or or just need more practice but its building up the formula i struggle with.

The "FX" tool is what i referred to with "Built-in". Its good in that you can break up the different functions and it reports live the result but it's not very good for more complex formulae like the one specified.

I was hoping there might be a website or some other software tool which has been designed specifically for building the formulae - it would seem simple enough to design for someone knowledgeable enough....

Thanks Again, Rep Added

[MrShorty]

To my knowledge, no, nothing better exists for Excel. From what I can see in Excel's documentation, Excel's function wizard has not really changed since my version 2007.

If you are not limited to Excel, I know other spreadsheets have tried to build better function wizards. LO calc's function wizard includes a "structure" tab that shows your formula in a kind of "tree" so you can see how your functions are nested together and provides allows you to see each nesting level within the function wizard style view. Gnumeric has a "function guru" that is somewhat similar.

As the market currently stands, if you want a better function wizard, I think you need to look to spreadsheets other than Excel.