Excel Help Thread

I always start with a simple index match formula just to test it and understand. Step through the calculation so you can get a feel for how it takes a value and looks up an array. Once you understand that it's returning the index you want, you just have to point to another array and use that index.

It works similar to a Vlookup or Hlookup but it doesn't have to be formatted with your lookup value in the first column or top row.

 

I can probably look at it tomorrow. Write something up with what you need or an example calculation. Pm me and I'll send you my email so you can share the file.

 

Bunch of basics in here still using excel for BI stuff

 

Can you just share the workbook? Only issue there is if people are updating it at the same time.

 

a snapshot of the data may be helpful to see how it's laid out in the spreadsheet.

 

A rudimentary way, but maybe create a column to the side that says =if(AQ2=number you need,"yes","no") then filter by yes from that column

 

This may work?
http://www.techonthenet.com/excel/filters/advanced1.php

 

Use an advanced filter for this.
Basically, make a list of all the things you want included, then filter in place or into a separate list.

It's pretty simple, just google "excel advanced filter".

 

Look at this guy

Loveyoubro

 

have 3 columns of data and want to make a scatter plot chart where each row is its own series. I know I have used a macro for this but can't remember/find the spreadsheet. halp.

 

Don't think you need a macro, just Insert>Charts>Scatter

 

I need each row to be its own series. So I could add a series for each one, but I have about a thousand rows

 

Insert>Charts>Scatter
Then right click on the chart...
Select Data>Switch Rows/Columns

 

figured it out

Spoiler

Sub CreateChart()

Dim lngRow As Long
Dim lngStartRow As Long
Dim objChart As Chart
Dim objSeries As Series

Set objChart = ActiveSheet.ChartObjects.Add( _
100, 100, 400, 250).Chart
objChart.ChartType = xlXYScatterLines

lngStartRow = 2
lngRow = 2
With ActiveSheet
lngStartRow = 2
lngRow = 2
Do While Len(.Cells(lngRow, 1).Value) > 0
If .Cells(lngStartRow, 2) <> .Cells(lngRow, 2) Then
' deal with grouped ID
Set objSeries = objChart.SeriesCollection.NewSeries
objSeries.Name = .Cells(lngStartRow, 2).Value
objSeries.XValues = Range("D" & lngStartRow, "D" & lngRow - 1)
objSeries.Values = Range("C" & lngStartRow, "C" & lngRow - 1)
lngStartRow = lngRow
End If
lngRow = lngRow + 1
Loop
Set objSeries = objChart.SeriesCollection.NewSeries
objSeries.Name = .Cells(lngStartRow, 2).Value
objSeries.XValues = .Range("D" & lngStartRow, "D" & lngRow - 1)
objSeries.Values = .Range("C" & lngStartRow, "C" & lngRow - 1)
lngStartRow = lngRow
End With

End Sub

 

this is really close to working - I may play with it some more. thanks.

 

Y'all need to graduate from Excel and get into some easier shit.

Download powerbi and start learning some basic DAX.

 

nm got it

 

Is it always going to be the same length?
If so, something like this:
=CONCATENATE(LEFT(A1,3),"-",MID(A1,4,3),"-",RIGHT(A1,4))

 

Yeah.

I got it - I just used the Custom Number Format of "####-####-##"

 

So is it possible to have get a formula that is going sideways, to ref cells that are going down on another sheet?

Like sheet 1 has month by month averages going from columns B to M.
Sheet 2 has monthly averages going down.. ie E1-e12.

I'm trying to get the formula on sheet 1 to go across while the cells it's referencing go down. I have not been able to figure out a way to do that.

 

Sumifs

 

Some type of Index Match would work

 

Ok here is my issue, I am trying to do a couple SUMIF statements, one seems to be working, the other doesn't.. so I am not sure what is going on.

formula in C7 =SUMIF(Transactions!V:V,">-1",Transactions!M:M)
formula in C8 =SUMIF(Transactions!N:N,"not yet received",Transactions!M:M)

my problem is cell C8. As seen in B8, there are 1029 records showing "not yet received" in the 'Transactions' tab, in range N:N. I want to sum all those transactions "not yet received".

 

There's probably something wrong with the way you typed "not yet received"

 

What does it tell you the error is?

 

My guess is there's something fucked up with Transactions!N:N

 

so if the formula in B8 works, =COUNTIF(Transactions!N:N,"not yet received", and and gets the count right seems odd my sumif isn't working.

I guess what do you mean by fucked up?

 

Actually maybe there's a bad value in the sum_range? Idk, it's hard to tell without having the Excel file. There's something that is breaking the SUMIF function, because it looks like your syntax is good. There's gotta be something wrong in the ranges. Maybe a number is saved as text instead of number, or something.

You could try
formula in C8 =SUMIF(Transactions!N:N,"not yet received",numbervalue(Transactions!M:M))

 

getting this error with that formula:

 

I'm thinking column M is fucked up

prob these #DIV/0! errors?

 

Great tip Excel. Seems like it doesn't recognize the SUMIF function? Dunno.

 

Yeah that's likely gonna mess it up.

 

this is what I get for trying to fix a spreadsheet that someone else made that no longer works here

 

Pivot table would probably be much easier than what you are doing

 

It's a lot more complex than what I shared. I wish it was that easy. By 5pm today I came to the conclusion that a lot of data has been manipulated and jacked up causing all these errors.

 

alright got another question, probably straight forward but not for me right now in my mind...

what I am trying to do is if the date in Column A, sheet1 is a 2015 or 2016 date, pull in from sheet 2 the corresponding "Code" (column A) column's value for 2015 or 2016 from sheet 2 to sheet 1. i'm not sure if that makes complete sense or not.

 

Why even bother with a new column, just use Year(A3) in the original equation

Also need to make sure the cell is formatted as Date

 

what equation am i going to need to use for this?

 

What are the names of the sheets

 

let's just call them sheet1 and sheet2 as i have in those screenshots.

 

I've got an easy one because I'm a total noob.

I work in a job that isn't finance related, but work closely with a lot of finance guys who use excel. Because of this, I'm trying to create my own spreadsheets for random shit to learn by doing. One of them I've made is for my gambling winnings this year. I want to build a formula that will give me a weighted average winning percentage. For example, I've placed 3 bets so far, and I'm 2-1. But I've placed bets of $30, $11, and $33 on them. I want to weight my win percentage by the dollars bet, calculated automatically. I can't for the life of me figure out how to do this in excel (I have it on Google Sheets but I'm sure the answer is similar). Pic of sheet is attached below.




Would also love to know how i can code the 0/1 to be L/W but I'll save that for another day.

 

For 0/1 to be L/W:

=if(A1=0,"L","W")

Basically the if statement is saying if cell A1 =0, it will show L. If it's anything else it will show W

 

Can I make it such that I can just enter W or L in that column and have them equal 1 or 0? Or do I need a separate column where I enter 1, 0 and it shows W/L in a new column?

 

I take it you're just trying to show the percentage correct?

Try:

=COUNTIF(G1:G3,"W")/COUNTA(G1:G3)

 

You can make it part of the formula in the "Net" column.
=If(g4="W",E4-D4,0-C4)

 

What do you want the answer to be based on the three bets so far?

You bet a total of $64 and won $30, so I'm assuming that you want a win % of about 75%?
Betting $64 and losing $64 would be 0%, right?

if so, it's just something like = (Total Winnings + Total Bet) / (2 * Total Bet)

I guess that was more of an algebra answer than an excel answer, though.

 

So you want your wins to be weighted against how much you money you bet?

that should work