User Profile
shawb
Copper Contributor
Joined Jan 15, 2020
User Widgets
Recent Discussions
Re: IF FUNCTION HELP
What you've written is a dynamic array formula--If excel is giving you a spill error, your formula wants to return a range and there are values in adjacent cells preventing it from doing so. Clear up some space around the cell your formula is in. Do you mean that Jackie can be associated with 700047 or 70058, etc? If so, you want to pop those inside the OR function. Also, remove the range and just leave A2 and drag the formula down column B. IF(OR($A2=70047, $A2=70058), "Jackie",IF(OR($A2=70053,$A2=70057),"Frezell",IF(... this will solve your issue of the array formula and prevent spill errors. When you drag the formula down column B, A2 will change to A3 in the next row.1.7KViews0likes2CommentsRe: Return Address of Max Value in a Range --modification needed
Riny_van_Eekelen Thanks for this reply. I think your perception of the formulas being difficult to manage is simply because you didn't write them... obviously, I do not find them difficult to read. Of course I do admit I may have complicated things more than necessary.... This bit you suggested: =MATCH(MAX(Facility!E16:E35),Facility!E16:E35,0)+2 SUPER HELPFUL. Honestly, I don't know what was going on with me that I couldn't think of that. I find your other suggestions irrelevant or impossible for what I need to get done. The Facility and Project sheets are filled out by 80+ agencies out in the US...I can't control how data is input into them and I can't control what version of excel it will be opened on. Thus, the max floor area always needs to be computed and I can't use the IFS function. Also, C16 on the QA sheet needs to return 7 different messages w/o using IFS--it'll remain long. Thanks though!1.9KViews0likes2CommentsReturn Address of Max Value in a Range --modification needed
Hi all, I've programmed a worksheet to run a set of quality assurance checks on data from other sheets in the book... In the attached file, the QA assesses data (view sheet notes for details) on the Facility and Project sheets and uses conditional formatting to highlight flags/concerns. Starting in C16 on the QA sheet, I'm using the following set of functions within a larger formula to return the address of the building type on the Facility sheet with the largest percent floor area: INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")) So far, this works fine, EXCEPT in one scenario... MY PROBLEM: Match returns the row number in terms of the lookup array so in order to get "Facility!E17" instead of E1, I believe I need to use the lookup array highlighted above I run into an issue when the max value (aka lookup value) is 1 (100% floor area is max) because participants can enter 1 as a response in Facility!E13:15 (Feel free to play with the Facility data to see how this affects QA) I really don't know what I can do differently so I need help!! How can I modify either the formula (preferable) or the cells on the facility tab so that a 1 is distinguishable from 100%? The building type cells have data validation for decimal 0 and 1 and are formatted for percentages. Since the QA sheet needs to be pasted into 60+ similar files, I'd rather not have to do too much manual manipulation to get this resolved.Solved2.1KViews0likes5CommentsWord crashes when trying to view chart templates
Hi all, Trying to insert a chart using a pre-saved template but every time I click "Templates" in the "Insert Chart" dialogue box, the entire program crashes... Any ideas? Just brought my Office 365 up to date and the problem persists.1.6KViews0likes1CommentCurly brackets added to formulas once workbook is copied and emailed out
[SOLVED] Hi all, I'm having an issue where my formulas change after the workbook is opened on other PCs. For example, here's how a formula appears in my workbook, where I calculate with just the enter key stroke, no CSE ever used: =IFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes"),"Both",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="no",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0)),"Heated Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="No",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0)),"Cooled Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0,OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0),"Fields left blank") The same formula appears as follows after I protect and hide cells, protect sheet, save, copy, attach to email, send: {=IFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes"),"Both",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="no",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0)),"Heated Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="No",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0)),"Cooled Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0,OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0),"Fields left blank")} Where do the curly brackets come from? It returns a VALUE! error because the formula does not compute with CSE shortcut... On the recipient's PC, as soon as you put the formula in edit mode, the curly brackets disappear and the computation happens normally. Plus, I don't think this formula can return an array given that I've populated the width and length returned by the offset function. Any ideas?Solved4.7KViews0likes1Comment
Recent Blog Articles
No content to show