Help - Excel formulae/functions

[Jux]

I am trying to make a spreadsheet for my office, which is quite complicated in some ways, and I am stuck.

Column B is text, name.
Column F is Total cost.
Columns G, H, I etc are numeric containing various calculations (mainly nested IFs).

I need to identify a name in Column B, and then nest a load of IF calculations in each row that the name occurs in.

How do I identify the rows? What function do I use?

[snuffykins]

Do you need to do different calculations for different names?

[tribpot]

Yes, I think it depends on what you need to do, i.e. are you looking up a list of values from elsewhere, e.g. a list of names.

Let's say you only needed to find "Jux", I would use Search but as this will return an error if it doesn't find "Jux", I would embed it inside an IF(ISERROR( - so you'd probably do nothing if it doesn't return Jux, i.e. if IF(ISERROR() returns true, then do whatever it is you actually want to do in the second clause, for if it evaluates to false.

I might write a custom function, though - it just depends what you need to do. I might also try to avoid doing text lookups, perhaps by assigning everyone a numeric code, which you could calculate with a VLOOKUP. Why are you trying to get text out of a string?

[Jux]

Not quite. There are a number of variants on some of the names, Company A, Company B, etc, and then there's Society A, B etc, and so on.

So, in column A, I have the names, Company or Society etc.
Then I have a number of columns for various numeric data, some of which are entered, and some calculated using formulae.
Then I have the last column, which will pick out all the "Company" records (A, B, C etc), but not the Society records, and perform another set of calculations.

Does that make sense?

[Jux]

X-posted Tribpot! I shall read your post now.

[Jux]

I can't assign numeric codes to the names, though that would make it easier for me. It would completely freak out everyone else so wouldn't be helpful.

When you say you'd write a custom function, what exactly does that mean?

[Jux]

I think I want a column, say M, which does this:

If cell A1 contains "Company"
and if B1 is less than N then calculate (C1*D1)/E1
but if B1 is greater than NN then do these calculations using cells F1 G1 H1 etc,
but if cell A1 doesn't contain "Company" then just leave this cell (M1) blank,
and if B1 equals something else, then do something involving lots of IFs


[aaaaargh!]

I probably need to look for cells containing Company * in order to be sure it picks up all the Company records.

[Jux]

I've already got a load of columns doing things like it, but without the bit about the name. It's finding a way to do that sort of IF stuff on just those specific records.

[snuffykins]

Will the ones you want have the same text at the same point in the name? For company a, company b etc you could specify if(left(B2,7)="company", blah blah blah)

[caroldecker]

You could assign a numeric to each company in a hidden column

[MeDented]

Can you add an extra column where you select a category for the name, this could be from a drop down list to restrict answers eg company, society. Then use the value of this col to determine the formulae to be used in other cold

[Jux]

Snuffykins, that may well work! Thank you.

I can't assign a numeric value, caroledecker, it won't work for the people I work with, and I've just realised that I actually have no idea how many assignations that could amount to and the record keeping for thousands and thousands just for that would be silly! There are only 3 of us.

MeDented, afraid not! Too many possibilities for it to be feasible. My use of Company A, Society B etc was just to illustrate that I need to identify certain records which have a part of the data in the "name" field in common. Those are the ones I want to do this particular calculation on, but it is in no way a true picture of the full data set.

[Jux]

OK, the "name" column, Column B, could be shops for instance (it isn't actually but hey). So I want all the Tesco instances, whether they're Tesco Metro, Tesco Superstore, Tesco USA, you get the picture.

Assigning values to different shops would be fine if we were only doing supermakets, but imagine I've got data records for as many different shops -large and small - as there are in your nearest city (not St David's!), as well as shops from all over the world. And then, not all of them will appear each month. In fact, apart from Tesco, perhaps Sainsbury's, and ooh Bloomingdale's, the other shops may only appear twice over several months. So we have hundreds and thousands of the records everymonth, and except for a few shops which appear most months, many will be infrequent visitors to our spreadsheet.

And a few shops (for which there will be multiple records) have to be treated differently, and have different calculations made.

So I want to find those shops and do evil nested IFs on them!

[Jux]

So at the moment I have

IF(FIND("Tesco",B1),"Bottoms", "burp")

Which almost works. If Tesco appears in the name column, it will output Bottoms, but it doesn't want to burp if Tesco doesn't appear, it just gives me #VALUE! instead.

But it's too late and I'm going to bed and will continue my quest for burps tomorrow (when both words will be changed to a bunch of IFs and horrid calculations).

Thank you all. If anyone has a better way of doing it I would be delighted to know. I have never done a spreadsheet like this before, so am quite pleased I've got this far.

[tribpot]

That's why you need the IsError function to cope with Find returning an error rather than False, Excel can't evaluate your IF clause.

Can you not do so this working in a hidden sheet, so your colleagues aren't aware of numeric codes etc? It sounds like the rules around what should be grouped and what not are somewhat arbitrary. How are the company names ending up in the worksheet, presumably someone is entering them somewhere.

A custom function would be one you wrote in code, but this doesn't have to be scary, have you ever recorded a macro?

[Jux]

I wasn't happy with that. It looks like it doesn't actually have enough terms in it; it needs something else on after that last closing bracket? I think I'll ask for Excel for Dummies for Xmas, as it's quite interesting and I'm sure work will need more of this kind of thing done.

The ss is large enough as it is to make it clumsy for those who need to use/consult it, so a hidden sheet sounds a good idea. I'll be looking at that a bit later, and may rethink what I've done so far and redo it. Thanks.

I have not been much of an Excel user until now, and my programming days were over long before PCs were around, so this is all quite fun! I think if it had been like that back in the 70s/80s I might have stuck with it

[tribpot]

Yes - it wasn't a complete formula, it was just a bit to get you going. Just booting up my Windows machine now so I will write out a complete formula.

It does sound like a different approach is needed here. I recently worked on a spreadsheet with c. 10,000 lines of VBA code behind the scenes, it was hosting two, arguably three, different applications and involved some seriously clunky integration with Word and two other spreadsheets as well. My top tip is: don't do that

But I think I would be very tempted to try and simplify all this formula stuff with some VBA, not least because all these nested IFs are going to execute every time the spreadsheet opens or a cell is changed. Potentially v painful.

Happy to take a look at a sample spreadsheet (I appreciate you can't share a real one) - Excel programming is rather fun.

[tribpot]

Okay, in Cell B1 I have entered the text "This is cell with the word Mumsnet in it". In Cell C1 I have the formula =IF(ISERROR(FIND("Sausage", B1)), "NOT FOUND", "FOUND")

This will display "NOT FOUND" as the word Sausage isn't in B1. If I change the formula to =IF(ISERROR(FIND("Mumsnet", B1)), "NOT FOUND", "FOUND") it displays "FOUND".

This is not massively intuitive because what it means in English is "if the string isn't found, do x. If the string is found, do y", i.e. you have to put what to do in the error case first.

You can flip the logic round by adding in a NOT, so =IF(NOT(ISERROR(FIND("Mumsnet", B1))), "FOUND", "NOT FOUND")

[Jux]

Tribpot, thank you thank you thank you! This is rather fun! I quite like the sound of your massive spreadsheet while still being quite glad I didn't have to do that

I didn't even know there were such things as hidden sheets until yesterday.

I am going to play with a few of these commands to see how they work, and think about changing my approach, try to simplify things.

You are one of those people my brother told me of - there are people who can programme in Excel, and then there are people who know how to use Excel properly, and apparently you are the Gods of Excel! I prostrate myself in your presence

[tribpot]

Yeah, the massive spreadsheet was quite something else, and I should say I didn't invent it - it was completely the wrong way of trying to achieve its aim. That's the key thing for you as well - work out what it is you really need it to do and then find the simplest way to achieve that.

From what you've said so far, I'd definitely want to look at how these names are being entered into the spreadsheet in the first place, can they be standardised (input form with a drop down list?) so you're not having to do text searches. Are you relying on some kind of string like 'Limited' to pick out the limited companies? That could get messy.

caroldecker mentioned hidden columns as well - this is a good way of being able to make your formulae simpler so it's easier to debug the spreadsheet, so instead of having 17 nested IF statements all in one cell you can break it down into smaller calculations but then hide the columns you don't want your co-workers to see.

If it's only you who needs all the fearsome calculations, you could in fact have two spreadsheets - one that contains references to the other - so as to keep the version that other people need to use smaller and quicker to run. But definitely have a look at hidden columns/sheets - and protected ones as well, so that people can't accidentally overtype your formulae.

[Jux]

Those tips are great, I am definitely going to get Excel for Dummies.

I have no idea at the moment where the data comes from, but it's not entered by hand. I suspect it is sent from various consultant-but-not-really-consultants and is somehow automatically put into one ss (assuming that's possible). I only work pt, and today is not a work day for me, so I can't find out until tomorrow.

caroldecker, I suspect you are also a God of Excel and I therefore sacrifice in your honour.

Anyway, with your help, I have done what they need for the moment, and I shall leave it being clunky until I've been able to find out more about Excel and how they get the data.

Thnak you.

[tribpot]

Great idea about Excel for Dummies - you can get chunks of it online but it will be easier in book form as it will take you through various exercises. If you've solved the immediate problem that's great.

Do come back if you have any more questions, though - I haven't had an excuse to do any Excel geekery for a few months. I was creating web pages from my uber-spreadsheet at one point, all using custom VBA. Good times. I haven't managed to persuade anyone this is the future of web publishing but I'm sure I will in time.

[caroldecker]

try not to use lots of nested formulas, it is better (and quicker and easier to find errors) if you do small calcs in different cells one after another.

You can also put the calculations on a separate tab, with only the final answer appearing on the front tab - all the other tabs can (should) be hidden and protected so muppets other staff cannot ruin your good work.

People who design excel 'models' will usually use: input sheets, calculation sheets and output sheets. These are colour coded/hidden are a easier to explain to people how they work.

[tribpot]

Yep and I think if you're trying to (say) calculate different VAT rates, I would use a VLOOKUP to get the relevant percentage and then grab that from the VLOOKUP table to put in the formula. But definitely, definitely split the calculations out across multiple cells.

Have you documented how you've designed the formulae, so that someone else could pick it up if they needed to?

[caroldecker]

Yes yes to documenting what it does and how - in my (old) role as an auditor, the number of companies using spreadsheets designed by people who had left and handed over only the input function with no understanding of the calcs or output. Many errors were caused by this (for example changes in VAT rates not recorded)

[Jux]

Ah, yes, documentation, innocent whistle

It shall be done tomorrow when I'm at work. No, I really will. Really.