Microsoft Office Forums Understanding an Excel formula
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-11-2022, 02:37 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58
Understanding an Excel formula

Good morning

I have been given a spreadsheet and, while I can see what the formula does, I don't understand how it is working. Can somebody please explain, step by step?

=(IF(C139=0,,C139&"
")&IF(J139=0,,J139&"
")&IF(K139=0,,K139&"
")&IF(L139=0,,IF(L139=M139,,L139&"
"))&IF(M139=0,,M139&"
")&IF(N139=0,,N139))

C = Name of store
L = Town
M = County
N = Post code

Many thanks
#2
05-11-2022, 05:09 AM
 wiganken Windows 11 Office 2019 Expert Join Date: Jul 2018 Posts: 279

It might help others to help you if you post the spreadsheet so that people can see the layout and understand what you are referring to.
#3
05-11-2022, 06:55 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

Here is a line from the spreadsheet
Attached Files
 Book2.xlsx (8.9 KB, 11 views)
#4
05-11-2022, 08:36 AM
 wiganken Windows 11 Office 2019 Expert Join Date: Jul 2018 Posts: 279

Seems gibberish to me. If 139 refers to a row number then that row is not populated so errors should be flagged up. Also the six strings are all in separate cells and don't look connected.

#5
05-11-2022, 08:49 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

Oh dear! The final formula is in one cell but I couldn't work out how to copy and paste it. This cell is linked to a mail merge in order to process an invoice. I think it is saying if a cell is blank, don't print anything on that address line (no zeros or blanks). For example, if there is no Address2, print the contents of the next cell (Town).
#6
05-11-2022, 10:47 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 749

As you data are really in row 2 in your example table, I replaced all references to row 139 with references to row 2;

As obviously you don't want to send a mail to non-existing store, I added a condition, that store name must exist to address string to be composed at all. It would be a real pain to check for every step in formula the previous part of string to be not empty;

In your attempt, in case it would work, you'd get a string like "Disco Drug StoreMeadow LaneBrightonSussexME2 6RL" - looks like a gibberish to me! To avoid this, I added delimiters (a comma followed with space) between all non-empty components of result string. You can change those delimiters in any way you want. The example where delimiter for Postal Code is changed to ", Postal Code ", and the code is between apostrophes, is presented with formula in Q3.
Attached Files
 FormulaExample.xlsx (9.6 KB, 9 views)
#7
05-12-2022, 01:00 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

I am afraid I haven't been able to explain myself well enough. The spreadsheet is used to produce invoices. The invoices have two boxes - the first contains the invoicing name and address(the owner or a managing company). The second contains the store location.

The "Invoice To" box picks up the details from the cell containing the formula I sent. In cases where there is a management company or owner, I overtype the formula with the different name & address. The "Location" box picks up the details from the individual cells C to N.

It works - my invoices are correct. My question was why does it work? I don't understand how to read the formula and wondered if someone could explain the steps to me.

Never mind - as I said, it works so I'll just go with it.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post pcrjyh12 Excel 3 12-01-2016 07:54 AM muna Word 1 10-27-2015 06:39 PM Lagrange Word 3 08-03-2015 11:06 AM toasty Project 1 07-21-2015 01:06 PM lhuffst Mail Merge 1 02-13-2013 05:19 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:43 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top