Search results

Hey everybody -- a page layout question: When printing, Excel (by default) aligns the print area with the Top Left page margin. The Page Setup->Margins menu gives the option to Center the print area Horizontally or Vertically, which I find a helpful function. Today, though, I want my print...

@azumi -- Great workaround, and a great solution for the example I provided. However, my actual application isn't always a "sum" function -- sometimes I need to return an array of text strings and further evaluate them, so I'm looking for a way to pass an array as the first argument to a...

Given the data in range A1:B6: black, 2 brown, 1 green, 3 orange, 6 pink, 3 purple, 2 and a set of test values in range D1:D10 green purple pink orange purple brown black green orange pink When I create an array formula: {=MATCH(D1:D10,A1:A6,0)} The formula returns an array...

Julius, Here's what I've got: in cell F3: =IF(B3="yes","n/a","") in cell G3: =IF(OR(B3="yes",AND(B3="no",D3="cash")),"n/a","") in cell H3: =IF(OR(B3="yes",AND(B3="no",C3=2)),"approved","") See attached.

Mine changes accordingly: =SUMPRODUCT((A2:A1000=E1)*(B2:B1000=MAX((A2:A1000=E1)*(C2:C1000=E2)*B2:B1000))*(C2:C1000=E2)*(D2:D1000)) See attached. But you'll note that you have to "double enter" the new criteria in mine, as compared with Luke's - I think his is the better alternative.

Daniel, Another alternative? =SUMPRODUCT((A2:A1000=E1)*(B2:B1000=MAX(IF(A2:A1000=E1,B2:B1000)))*(C2:C1000)) Also an array formula (like Luke's) - requires Ctrl+Shift+Enter See attached. @Luke M -- Cool way to extract the row number! I appreciate your example.

Welcome to the forums! Something like this? =IF(Budget-Actual>=0,Budget-Actual&" Under", Actual-Budget&" OVER") =IF(A2-B2>=0,A2-B2&" Under", B2-A2&" OVER") See attached.

Sounds like you want to set up a mail merge? https://support.office.com/en-in/article/Use-mail-merge-to-create-and-print-letters-and-other-documents-f488ed5b-b849-4c11-9cff-932c49474705

OK -- I misunderstood your requirement. Try this array formula in cell A2: =698+SUM(1/COUNTIF($B$2:B2,$B$2:B2)) Also, change your cell formatting to: "BBE"0000000 See attached.

Will, I'm impressed (and a little bit motion sick ;)) reading your Nested If: 1558 characters. -- woah. It does the job, I guess? It seems like it would be somewhat cumbersome to work with...I hope you won't mind my suggesting an alternative? Since you already reference a "Categories" tab...

Try this in cell d2: =IF(A2=A1,"",A2) Drag down to fill. See attached.

Would it work to search and replace any occurrences of "exchange" first and then search for the substring "change"? =FIND("change",SUBSTITUTE(B2,"exchange",""))

Try this formula in cell E7: =SUMPRODUCT(($A$7:$A$30=A7)*$B$7:$B$30) See attached.

I'm embarrassed. I can't be very helpful on this thread...Please excuse my initial irrelevant reply.

Sarfraz, See attached -- I set up a conditional formatting formula I set up in the BS tab to highlight items that will require reconciliation: =AND(ISNUMBER(C3),ISNA(C3=INDEX(BL!$D$2:$E$500,MATCH(IF(ISNUMBER(C3),$B3),BL!$C$2:$C$500,0),5-COLUMN(C3)))) You'd probably want to develop a similar...

Kellis, I think that the formula you want is: =YEAR(EOMONTH(B2,-3)) Where B2 is the target date... See attached.

Orlando - welcome to the forums! Can you provide the desired outcome for the example you provided so we can test that the formulas we compose actually achieve the output you want? I've got an example for you (attached) - but I've just guessed about how you want to apply the parameters you've...

Hi mgao77, I propose an array formula to calculate the average you're looking for. See attached; I think it's getting at what you want... You have a little bit of ambiguity in your request, which makes it difficult to understand exactly what you want. Suppose, for example, that a client...

Drew, Welcome to the forum! I'm trying out some solutions to your problem -- See attached. Is this doing what you wanted? All best.

Welcome to the forums! See attached file -- I propose a formula like this: =INDEX(B2:H14,MATCH(B17,A2:A14,1),MATCH(B18,B1:H1,1)) Where your data is in B2:H14 Your distance thresholds are in A2:A14 Your weight thresholds are in B1:H1 Your distance input is in B17, and your weight input is...

Thanks for the link. It's similar in many ways to the link NarayanK suggested earlier in the thread -- On one hand it's very helpful: It introduced me to some aspects of VBA that I haven't encountered before...and, with this new information, I was able to pickup Conditional Formatting color...

You're doing it right -- -- and your formula is returning the right answers: {FALSE,TRUE,FALSE,FALSE} But only the first result displays in the cell where you type the formula. What is the desired/expected output?

Lolo, What a headache! I recently had the same challenge, and found (like you) that there isn't an easy solution. I was glad to see your post - albeit a few months too late to be of much help to you, I suppose. Here's my proposal: I discarded the idea of tying to the CF color, and instead I...

Correct -- when a macro runs, you lose the ability to "undo" previous actions in the file. That's one downside of using macros. If you happen to run the macro and then realize a major mistake, the only solution I know is to close the file without saving changes; or to "Save as" with a new...

I've modified the conditional formatting in your original file accordingly...See attached.