Ask me anything about Microsoft Excel - Page 103 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

Two Plus Two Forums 2+2 Communities Other Other Topics

Ask me anything about Microsoft Excel

Post Reply Subscribe

...

Page 103 of 120

First

3 53 83 93 98 99 100 101 102 103 104 105 106 107 108 113

Last

Page 103 of 120

First

3 53 83 93 98 99 100 101 102 103 104 105 106 107 108 113

Last

02-23-2016 , 12:44 AM

#2551

mikeymer

veteran

Join Date: Jan 2005 Posts: 3,158

Can someone recommend the best way to get acquainted with VBA to use in a finance setting? Thanks.

Quote

02-23-2016 , 11:09 AM

#2552

lkasigh

adept

Join Date: Jun 2013 Posts: 953

Quote:

Originally Posted by mikeymer

Can someone recommend the best way to get acquainted with VBA to use in a finance setting? Thanks.

Just trial and error. Identify your goal and try to find a way to make it work.

If you get stuck, usually Google can put you on the right track.

Quote

03-01-2016 , 08:21 PM

#2553

Acemanhattan

veteran

Join Date: Jan 2007 Posts: 2,978

I need to find a way to flag the max value of subsets of data with a 1.

So I have an identifier in column A10:A100 a column of base rates in B10:B100 and a column of values that change dynamically in C10:C100 that change dynamically depending on inputs in C1 C2 and C3 (as an example C10=B10*C1; C11=B11*C3; C12=B12*C1; C13=B13*C2 etc, so the change to column C10:C100 isn't necessarily constant.)

What I want is to have a column in D10: D100 that flags the max observation in each subset of A10:A100 with a "1" as the values in C10:C100 changes.

Thougts?

Quote

03-01-2016 , 08:32 PM

#2554

LostOstrich

Carpal \'Tunnel

Join Date: Jan 2008 Posts: 15,933

in cell A101, use

=max(A10:A100)

then in D10,

=if(A10=A$101),1,""

and copy D10 down to D100. This will identify the max value in range A10-A100, and mark a 1 in the corresponding column D cell when this max value appears in A.

Hope I've understood your issue correctly! I see you mention subsets, so perhaps you can apply this method to however you're splitting up column A.

Quote

03-01-2016 , 09:16 PM

#2555

syndr0me

old hand

Join Date: Dec 2006 Posts: 1,682

Is there a way to have your pivot tables default to no sub totals and tabular form?

Quote

03-01-2016 , 11:45 PM

#2556

NxtWrldChamp

Carpal \'Tunnel

Join Date: Feb 2008 Posts: 9,082

Quote:

Originally Posted by Acemanhattan

From D10: D100 copy this formula

=IF(A10=LARGE($A$10:$A$100,1),1,"")

Quote

03-02-2016 , 02:07 AM

#2557

Acemanhattan

veteran

Join Date: Jan 2007 Posts: 2,978

I don't think I was clear enough. Column A has identifiers like Tom, Mark, Sarah, Geno, etc with the names appearing multiple times. I want to flag the max observation from column C within each subset defined by the identifier in A.

Quote

03-02-2016 , 09:26 AM

#2558

lkasigh

adept

Join Date: Jun 2013 Posts: 953

Quote:

Originally Posted by Acemanhattan

What I would do is create a separate section of the workbook (Let's assume it starts at A101) and list all the names once, then use this formula one cell over.

=LARGE(IF($A$10:$A$100=A101,$C$10:$C$100,0),1) - this is an array formula so you need to hit CTRL+SHIFT+ENTER or FN-COMMAND-ENTER on a Mac when entering it

Then use this formula in Column D: =IF(VLOOKUP(A10,[coordinates of the table you just created],2,FALSE)=C10,1,0)

It it is also possible to integrate it all into one formula in Column D, but separating it should be easier to understand conceptually.

Quote

03-02-2016 , 04:12 PM

#2559

CrazyEyez

Carpal \'Tunnel

Join Date: Sep 2003 Posts: 8,669

Still waiting for the MAXIF(S) function...

Quote

03-02-2016 , 04:19 PM

#2560

Faluzure

veteran

Join Date: Dec 2005 Posts: 2,986

I'm looking at a spreadsheet where a macro is written in a worksheet rather than the vba editor and I'm not quite sure what's happening. The main function of the macro, vfrac, is to convert decimal text into a fraction.

Show formulas:

Values:

I tried copying this into another spreadsheet to perform the same function but I can't copy the tab or copy paste the formulas because nothing happens. I'm not sure how this is even done. Any ideas?

Quote

03-02-2016 , 06:27 PM

#2561

Acemanhattan

veteran

Join Date: Jan 2007 Posts: 2,978

Quote:

Originally Posted by CrazyEyez

Still waiting for the MAXIF(S) function...

Agreed

Quote

03-03-2016 , 10:46 AM

#2562

MeLoveYouLongTime

Not a difficult personality

Join Date: Jun 2014 Posts: 13,159

I need some help please.

I'm measuring temperature in 100 locations every 10s for 1hr. The test will be repeated 3 times at different voltages.

I need to extract the highest temperature per location and keep the accociated time and voltage.

I've tried just sorting the columns, but I loose the time.

Quote

03-03-2016 , 10:50 AM

#2563

MeLoveYouLongTime

Not a difficult personality

Join Date: Jun 2014 Posts: 13,159

It would also be nice to seta temperature limit per location, and get an alarm when the limit is surpassed.

Quote

03-03-2016 , 11:05 AM

#2564

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

That's a MAXIFS problem like it was talked about above.
The reverse then is just a SUMIFS having the location and the temperature as a criteria (date and voltage are numbers that's why it works otherwise it would be different).

But I think your problem would better be solved with a VBA. It's the most efficient way.

The alarm is a simple IF including a MAXIFS.

If you post a data sample, I can have a look if I can implement a quick solution.

Quote

03-03-2016 , 11:21 AM

#2565

MeLoveYouLongTime

Not a difficult personality

Join Date: Jun 2014 Posts: 13,159

I'll post a lil later today...thanks!

Quote

03-04-2016 , 02:29 AM

#2566

BobJoeJim

Tournament Director

Join Date: Jan 2006 Posts: 11,200

You should be able to simulate the desired MAXIFS by nesting COUNTIFS inside an IF statement. You're looking for where the count of higher values than your criteria is zero, because then you have a max. Like if column A is "Team", column B is "Game #", and column C is "Score", and your goal is to determine in which game the team in A2 scored the most points, then in D2:

=IF(COUNTIFS(A:A,A2,C:C,">"&C2)=0,"Max score in game "&B2,"not a max" )

Fill down, etcetera. Adjust as needed for your columns and criteria. Voila, MAXIFS!

Quote

03-04-2016 , 03:41 PM

#2567

Acemanhattan

veteran

Join Date: Jan 2007 Posts: 2,978

I'm using the Mode(IF()) array function on a large group of data, and it works great except for when there are no duplicate values for a given criteria. In that case it returns #N/A. What I'd like to do is have it simply return the unique value as the "mode". Is there any way I can do that?

Quote

03-04-2016 , 09:40 PM

#2568

CrazyEyez

Carpal \'Tunnel

Join Date: Sep 2003 Posts: 8,669

Quote:

Originally Posted by BobJoeJim

Cool solution

(I'd still like a MAXIFS so I don't need to add helpers and nest a bunch of stuff for multiple parameters.)

Quote

03-04-2016 , 10:18 PM

#2569

Acemanhattan

veteran

Join Date: Jan 2007 Posts: 2,978

Quote:

Originally Posted by BobJoeJim

This looks promising! To throw a twist in there, what about the case when we get two maxes that are the same but we only want one of them (arbitrarily) to be flagged as the max?

Quote

03-05-2016 , 01:52 AM

#2570

BobJoeJim

Tournament Director

Join Date: Jan 2006 Posts: 11,200

Quote:

Originally Posted by Acemanhattan

This looks promising! To throw a twist in there, what about the case when we get two maxes that are the same but we only want one of them (arbitrarily) to be flagged as the max?

To identify multiple lines tied for the max, you could have the IF(COUNTIFS(>condition)=0) function be a helper column, and just return RAND() for maximums and "" for anything else, then you could repeat the MAXIF function in the next column to find the maximum RAND() result that also meets your other criteria, and have that be the one you ultimately return?

Clumsy and absurd, but it gets the job done, lol. I'll sleep on it and if I think of something that works without being ugly and dump I'll let you know

Quote

03-05-2016 , 03:50 AM

#2571

Acemanhattan

veteran

Join Date: Jan 2007 Posts: 2,978

IF(COUNTIFS($C$100:C100,C101,$BY$100:BY100,"Y",$BZ $100:BZ100,"1")=0,IF(COUNTIFS($C$101:$C$2199,C101, $BY$101:$BY$2199,"Y",$AV$101:$AV$2199,">"&AV101)=0 ,1,0.5),0.5)

I was able to accomplish what I was looking to do (though not arbitrarily, and not elegantly) by conditioning on whether or not your logic had placed a 1 (flagged as max) in the column cells preceding the current cell.

Quote

03-09-2016 , 11:06 AM

#2572

wonderkid23

adept

Join Date: Feb 2010 Posts: 724

I am doing some Ads testing and I am stuck at a specific problem.

I have stats on Ads (Impression, clicks, app installs) and I have determined the rate that each ad is clicked on and the rate that the app (that's advertised) is installed.

So essentially, Impressions are the sample sizes for each ad.

What I want to do is find out whether the difference in Click Rate between two ads is statistically significant. I have found this tool: http://www.cardinalpath.com/resource...-testing-tool/ and I would like to be able to do the same thing on Excel.

Any ideas?

Quote

03-12-2016 , 04:45 AM

#2573

validand notinuse

adept

Join Date: Jul 2012 Posts: 833

apologies if this is simplistic, not my strongpoint. i have a spreadsheet where im trying to setup an algebraic equation f43-(f42*x)=f46. is there a way to get excel to solve for x?

Quote

03-12-2016 , 09:11 AM

#2574

lkasigh

adept

Join Date: Jun 2013 Posts: 953

Quote:

Originally Posted by validand notinuse

apologies if this is simplistic, not my strongpoint. i have a spreadsheet where im trying to setup an algebraic equation f43-(f42*x)=f46. is there a way to get excel to solve for x?

if my algebra is correct it would be =(F46-F43)/(-F42)