Conditional Formula Selection in Excel

October 22, 2009 4:21 AM

How can I create an Excel worksheet which uses something like VLOOKUP (or anything which works in a similarish way) not to pull values, but formulas?

So, here's the situation: I'm trying to make a single billing Excel sheet where I can keep track of invoicing multiple clients. However, due to peculiarities of my industry (translation), the formula used for billing each client is different. Some round up to the nearest 100 words, some round normally to the nearest 100 (49 rounds to 0, 51 rounds to 100), some are billed X per 100 words, some are paid Y per 100 words.

I can make any one of those formulas fine, independently, but I'd like to have all clients on the same page, and use some sort of formula where I'd pick the clients name, enter the number of words in the translation, and Excel would look up in a table or something to determine that, for example, the formula for Company A is ROUNDUP(A2/100,0)*100*B2 but the formula for Company B is ROUND(A2/100,0)*100*B17 or the like.

Is there any way to do this without nesting IF clauses (which would become troublesome the more clients I got)?

posted by Bugbread to computers & internet (7 comments total)

See here. You'd have to use a very light bit of VBA to do this. Just add a VBA function that looks like this:

Function Evalu(ByVal S As String) As String
   Evalu = Evaluate(S)
End Function

You can then use =EVALU(A1) to run the formula that's in cell A1. This could obviously be extended by doing something like =EVALU(VLOOKUP(A1,D:J,4,FALSE)), which would run the formula in the fourth column of D:J that corresponds to the value in A1.
posted by Doofus Magoo at 4:54 AM on October 22

If roundup or round normal are the only options, I'd create a column for billingtype and one for wordsbilled. Billingtype could contain "up" or "normal" WordsBilled could then contain a single IF statement =if(A2="UP", round(...), rounda(...)) This would also make the process a bit more transparrent if you ever needed to know exactly why you billed a client a specific rate.

You might also want to create columns for rate and for the number you round to, so your spreadsheet would look like:

| BillingType | Rate Per set of words | size of set | WordsBilled | cost

BillingType and WordsBilled would be the same as described above, except instead of hard coding "100" in the round functions, you would reference the value in the "Size of set" function.

Cost would then become Rate * WordsBilled

I think this would make the data the easiest to read and work with, and give you some extra flexibility if a formula changed, like a big client who insists on being billed by 50 or 1000 word sets. It would also make it easy to generate a detailed invoice since all the values would be accessible.
posted by nalyd at 5:53 AM on October 22

Another option would be to create a table that calculates the bill for each client using all possible billing methodology, then use a VLOOKUP formula to select the correct value. You'd need to create a "Billing Methodology" field, which could potentially match the offset value in the VLOOKUP formula.
posted by grateful at 6:21 AM on October 22 [2 favorites]

Do what grateful suggests.
posted by orthogonality at 9:44 PM on October 22

Thanks for all the answers.

For right now, rounding up/down and the base rate are the only variables, so I was considering nalyd's solution, but in the future there might be more difficult formulas (minimum fees, discounts for high volume, etc.) which would make it really hard to adapt.

Grateful's idea was another that occurred to me, but I want to have all my billing for the year on one page, so I'd have to replicate that table tens if not hundreds of times (once for each job).

Doofus Magoo's suggestion absolutely nails it, though. I did a little test run, and the only problem I encountered was that it executed everything exactly as-is (i.e. if the formula said AVERAGE(B1:C1) then it would execute exactly that, even though I wanted it to be AVERAGE(B1:C1) on line 1 and AVERAGE(B2:C2) on line 2). For future reference (in case anyone ever stumbles across this with a similar question) I solved this by switching Excel from A1 referencing style to R1C1 referencing style and writing my formulas like AVERAGE(R[0]C1:R[0]C2). That way the exact same formula can be applied to any row and work fine (i.e. if I use that formula on row 1, it averages A1:B1, and if I use it on row 9, it averages A9:B9).

So thanks for all the help, this will make my recordkeeping much, much easier.
posted by Bugbread at 10:45 PM on October 22

And now that I've tried implementing it with actual formulas (not just the test run), let me say that it works GREAT!

Except for one little teeny tiny thing that I could totally ignore if its unfixable, but which would be great if it were fixable: the number it returns is unformattable. For example, I ran the formula on one set and got:

3450

That's exactly the number I wanted, but I'd like to have a currency mark and a thousands comma, like so:

Y3,450

That's usually dirt easy in Excel: right-click, select "Format Cells", format to your liking, and you're done. But none of the things I tested out there actually worked. I couldn't get the currency symbol to appear, nor the commas, nor to get decimal places. Any idea on how I can format it?
posted by Bugbread at 11:36 PM on October 22

Err...nevermind, I answered my own question: TEXT(ResultOfEvaluCommand,"#,###"). Sorry, I really did research before asking my first question, but I should have done some more looking before asking that followup question.
posted by Bugbread at 11:50 PM on October 22