Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-11-2022, 02:37 AM
Joanne Joanne is offline Understanding an Excel formula Windows 7 32bit Understanding an Excel formula Office 2010 32bit
Advanced Beginner
Understanding an Excel formula
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default 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
J = Address1
K = Address2
L = Town
M = County
N = Post code

Many thanks
Reply With Quote
  #2  
Old 05-11-2022, 05:09 AM
wiganken wiganken is offline Understanding an Excel formula Windows 11 Understanding an Excel formula Office 2019
Expert
 
Join Date: Jul 2018
Posts: 279
wiganken will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 05-11-2022, 06:55 AM
Joanne Joanne is offline Understanding an Excel formula Windows 7 32bit Understanding an Excel formula Office 2010 32bit
Advanced Beginner
Understanding an Excel formula
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

Here is a line from the spreadsheet
Attached Files
File Type: xlsx Book2.xlsx (8.9 KB, 11 views)
Reply With Quote
  #4  
Old 05-11-2022, 08:36 AM
wiganken wiganken is offline Understanding an Excel formula Windows 11 Understanding an Excel formula Office 2019
Expert
 
Join Date: Jul 2018
Posts: 279
wiganken will become famous soon enough
Default

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.

Maybe some expert will help you. Good luck.
Reply With Quote
  #5  
Old 05-11-2022, 08:49 AM
Joanne Joanne is offline Understanding an Excel formula Windows 7 32bit Understanding an Excel formula Office 2010 32bit
Advanced Beginner
Understanding an Excel formula
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

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).
Reply With Quote
  #6  
Old 05-11-2022, 10:47 PM
ArviLaanemets ArviLaanemets is offline Understanding an Excel formula Windows 8 Understanding an Excel formula Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
File Type: xlsx FormulaExample.xlsx (9.6 KB, 9 views)
Reply With Quote
  #7  
Old 05-12-2022, 01:00 AM
Joanne Joanne is offline Understanding an Excel formula Windows 7 32bit Understanding an Excel formula Office 2010 32bit
Advanced Beginner
Understanding an Excel formula
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Understanding an Excel formula Understanding Asterisk in IF functions pcrjyh12 Excel 3 12-01-2016 07:54 AM
Understanding an Excel formula Understanding SmartArt muna Word 1 10-27-2015 06:39 PM
Understanding an Excel formula Understanding Document Corruption Lagrange Word 3 08-03-2015 11:06 AM
Understanding an Excel formula Not understanding discrepancies in homework and need help toasty Project 1 07-21-2015 01:06 PM
need help understanding mailmerge code lhuffst Mail Merge 1 02-13-2013 05:19 PM

Other Forums: Access Forums

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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft