Open Side Menu Go to the Top
Register
Ask me anything about Microsoft Excel Ask me anything about Microsoft Excel

07-24-2012 , 05:33 PM
Quote:
Originally Posted by Canceler
Use the code wrap button (#), along with Preview Post, to make tables.

Code:
Column A     Column B
To:          Steve
To:          Bill
To:          Jim
Attach:      C:\book1.xls
Attach:      C:\book2.xls
Subject:     Daily Files
Body:        Let me know if you have questions
Nice, thank you.
Ask me anything about Microsoft Excel Quote
07-27-2012 , 11:47 PM
Why is the text on this page different than normal? When I resize a row the text just starts to get cropped out, like in row 4. Thanks.

edit- I just realized I only have open office on this computer. Maybe someone can help anyway.

Last edited by Sly Caveat; 07-27-2012 at 11:56 PM.
Ask me anything about Microsoft Excel Quote
07-30-2012 , 03:04 PM
I have a chart of store numbers and sales volume by day, by store, by aggregate. Can I make a pivot chart that will give me the top stores by aggregate?
Ask me anything about Microsoft Excel Quote
08-01-2012 , 09:32 AM
Is there a way to lock a row so that the input can only be numbers? Likewise, is there a way to lock only non-special characters?

I have two rows, one that should be a number, and one that should be initials. Without fail, people will put NA instead of 0 for the number, and N/A or J.K. (in addition to J.K, JK., JK) which makes sorting a real bitch.
Ask me anything about Microsoft Excel Quote
08-01-2012 , 09:41 AM
Data validation
Ask me anything about Microsoft Excel Quote
08-01-2012 , 05:14 PM
Quote:
Originally Posted by CrazyEyez
Code:
Option Explicit
Thanks for that!


I've a feed coming in that constantly updates a cell. What's the best way to find the high value it was for the day?

I have iterative calculations turned on and basically:

=IF(O4>O5, O4,O5)

where o4 is the feed, and o5 is the high. The circular reference can throw up errors though. Is there a cleaner solution?
Ask me anything about Microsoft Excel Quote
08-01-2012 , 06:31 PM
When I change the background color in some of the cells, the gridlines disappear. Is there any way to change the background color in certain cells (but not all of them) and keep the gridlines for the whole sheet?

Last edited by Cardfish1; 08-01-2012 at 06:33 PM. Reason: Excel 2007
Ask me anything about Microsoft Excel Quote
08-01-2012 , 10:49 PM
Four numbers in the same cell 32 : 19 (15:9). How do I put each number in a different cell.?
Ask me anything about Microsoft Excel Quote
08-02-2012 , 12:54 AM
this website generated an equation for me that I would like to be able to put into excel. It has three variables - x, y, and z. I want to be able to enter x and y and have it give me z. Here's the VBA code that website generated for the equation. Where do I put this in excel to do what I want to do?


' Fitting target: lowest sum of squared absolute error
' Fitting target value = 6536.61045481

Public Function RomanSurfaceMinus_ScaledAndOffsetXY_Offset_model(x _in, y_in)
temp = 0.0

' coefficients
Const k = -9.1674904936454288E+02
Const a = -6.0359321169675937E+01
Const b = -9.7925391600584177E+00
Const c = -3.8555428508265432E-01
Const d = 7.8968496022444151E+02
Const Offset = 2.0697055004533843E+02

var temp_x_sq = (a * x_in + b) * (a * x_in + b)
var temp_y_sq = (c * y_in + d) * (c * y_in + d)
temp = (k * (temp_y_sq - temp_x_sq) - (temp_x_sq - temp_y_sq) * Application.WorksheetFunction.power(k * k - temp_x_sq - temp_y_sq, 0.5)) / (2.0 * (temp_x_sq + temp_y_sq))
temp = temp + Offset
RomanSurfaceMinus_ScaledAndOffsetXY_Offset_model = temp
End Function
Ask me anything about Microsoft Excel Quote
08-02-2012 , 04:28 AM
Quote:
Originally Posted by crzyjoedavola
Four numbers in the same cell 32 : 19 (15:9). How do I put each number in a different cell.?
http://www.techrepublic.com/article/...ctions/1033367
Ask me anything about Microsoft Excel Quote
08-02-2012 , 11:26 AM
Quote:
Originally Posted by Your Mom
this website generated an equation for me that I would like to be able to put into excel. It has three variables - x, y, and z. I want to be able to enter x and y and have it give me z. Here's the VBA code that website generated for the equation. Where do I put this in excel to do what I want to do?


' Fitting target: lowest sum of squared absolute error
' Fitting target value = 6536.61045481

Public Function RomanSurfaceMinus_ScaledAndOffsetXY_Offset_model(x _in, y_in)
temp = 0.0

' coefficients
Const k = -9.1674904936454288E+02
Const a = -6.0359321169675937E+01
Const b = -9.7925391600584177E+00
Const c = -3.8555428508265432E-01
Const d = 7.8968496022444151E+02
Const Offset = 2.0697055004533843E+02

var temp_x_sq = (a * x_in + b) * (a * x_in + b)
var temp_y_sq = (c * y_in + d) * (c * y_in + d)
temp = (k * (temp_y_sq - temp_x_sq) - (temp_x_sq - temp_y_sq) * Application.WorksheetFunction.power(k * k - temp_x_sq - temp_y_sq, 0.5)) / (2.0 * (temp_x_sq + temp_y_sq))
temp = temp + Offset
RomanSurfaceMinus_ScaledAndOffsetXY_Offset_model = temp
End Function
Alt+F11
Insert>Module
Paste all that code
Change that function name to something shorter because it's ridic long. Also change it toward the bottom of the code where it says RomanSurface... = temp.
Back in excel you can now use that function with x and y as the parameters: =shortname(x,y)
Ask me anything about Microsoft Excel Quote
08-02-2012 , 11:50 AM
Quote:
Originally Posted by crzyjoedavola
Four numbers in the same cell 32 : 19 (15:9). How do I put each number in a different cell.?
If the number of characters are not always the same then you're going to have combine the string functions (left,right,mid) with Find and Len functions. For example to extract the 15:
A1
32:19(15:9)

B1
=FIND("(",A1)+1

C1
=FIND(":",A1,B1)

D1
=MID(A1,B1,C1-B1)

I seperated them for simplicity but you can combine them all into one. Also, wrap then end result in VALUE() so it treats it as a number rather than text.
Ask me anything about Microsoft Excel Quote
08-02-2012 , 03:32 PM
It's saying Compile Error: Sub or Function not defined and is highlighting the first Var. Here's my code:


Public Function Football(x_in, y_in)
temp = 0#

' coefficients
Const k = -9.16749049364543
Const a = -6.03593211696759
Const b = -9.79253916005842
Const c = -3.85554285082654
Const d = 7.89684960224442
Const Offset = 2.06970550045338

Var temp_x_sq = (a * x_in + b) * (a * x_in + b)
Var temp_y_sq = (c * y_in + d) * (c * y_in + d)
temp = (k * (temp_y_sq - temp_x_sq) - (temp_x_sq - temp_y_sq) * Application.WorksheetFunction.Power(k * k - temp_x_sq - temp_y_sq, 0.5)) / (2# * (temp_x_sq + temp_y_sq))
temp = temp + Offset
Football = temp
End Function
Ask me anything about Microsoft Excel Quote
08-02-2012 , 05:54 PM
Delete both the 'Var' and see if it works.
Ask me anything about Microsoft Excel Quote
08-02-2012 , 10:48 PM
I did that and then I had to enable macros and it worked. Thanks CrazyEyez. This thread is the greatest.
Ask me anything about Microsoft Excel Quote
08-08-2012 , 03:50 AM
I love excel 2010.

I would marry excel 2010 if I weren't already married with a kid. And excel 2010 were a woman.

Sparklines. Paste values and source formatting.
Ask me anything about Microsoft Excel Quote
08-08-2012 , 03:58 AM
Quote:
Originally Posted by CrazyEyez
I thought we could do tables within a post. I only see buttons for lists, not tables.
[table]
Rank Screen Name Omg Posts Prev Rk 1st Omg
1 Mayo 49,515 1 1
2 nham 43,773 2 19
3 The Brickie 41,401 3 36
4 allinontheturn 36,907 5 23
[/table]


creates this table


RankScreen NameOmg PostsPrev Rk1st Omg
1Mayo49,51511
2nham43,773219
3The Brickie41,401336
4allinontheturn36,907523
Ask me anything about Microsoft Excel Quote
08-08-2012 , 08:41 AM
Quote:
Originally Posted by gregorio
[table]
Rank Screen Name Omg Posts Prev Rk 1st Omg
1 Mayo 49,515 1 1
2 nham 43,773 2 19
3 The Brickie 41,401 3 36
4 allinontheturn 36,907 5 23
[/table]


creates this table


RankScreen NameOmg PostsPrev Rk1st Omg
1Mayo49,51511
2nham43,773219
3The Brickie41,401336
4allinontheturn36,907523
Thanks!
Ask me anything about Microsoft Excel Quote
08-08-2012 , 10:07 AM
Hi,

I have a sheet where I have a long series of numbers:

A
1
5
25
50
65
66
67
75
76
91

And separately, I have a second list of ranges:

AB
29
2030
6070
90110

I want to check if each of the first list of numbers falls inbetween any of the ranges in the second list of ranges.

Any hints?
Ask me anything about Microsoft Excel Quote
08-08-2012 , 11:29 AM
Josem,

I assume your range is not just 4 different checks otherwise you could do something like:

Code:
=IF(OR(A2=MEDIAN(A2,H$2:I$2),A2=MEDIAN(A2,H$3:I$3),A2=MEDIAN(A2,H$4:I$4),A2=MEDIAN(A2,H$5:I$5)),1,0)
Assuming your table is in column A and the second list is in H2:I5 if your second list is quite large i'd probably use a UDF if you are ok with using VBA
Ask me anything about Microsoft Excel Quote
08-08-2012 , 11:41 AM
VBA solution:

Go to VBA editor (right click on sheet tab > view code)
Add a module (right click on the project explorer > Insert > Module)

Paste:

Code:
Public Function inRange(chkValue, chkTable) As Boolean
    Dim topLeft As Range
    Dim bottomRight As Range
    Dim i As Long
    Dim fnd As Boolean
    
    Set topLeft = chkTable.Cells(1)
    Set bottomRight = chkTable.Cells(chkTable.Cells.Count)
        
    fnd = False
    For i = topLeft.Row To bottomRight.Row
        If chkValue >= Cells(i, topLeft.Column) And chkValue <= Cells(i, bottomRight.Column) Then
            fnd = True
            Exit For
        End If
    Next i
    inRange = fnd
End Function
Then use:

=inRange(A2,$H$2:$I$5)
Ask me anything about Microsoft Excel Quote
08-08-2012 , 12:32 PM
Thanks

The "real" data has about 150 ranges and 20,000 numbers to lookup.

Thus, I fiddled with the data and ended up using the system here which worked fine for me: http://www.jlathamsite.com/Teach/Exc...ndexLesson.pdf
Ask me anything about Microsoft Excel Quote
08-09-2012 , 02:38 PM
What's the simplest way of copying a few cells (not adjacent) to a separate worksheet using vba?

Don't need to open/close/do anything else with the sheets, they'll be open.
Ask me anything about Microsoft Excel Quote
08-10-2012 , 07:51 AM
Sorry if this has been asked already, is it possible to run a pivot table and chart off the same filters?
Ask me anything about Microsoft Excel Quote
08-13-2012 , 06:17 PM
Calculating average win rate per hour.
I exported my data from my i touch ( Poker Journal ) app via email and decided to give excel a try.
It was easy to figure out the "sum " function, and the total hours column added up correctly.
I have my total hours cell, and my total winnings cell, but when I divide the HH:MIN:SEC cell into the " winnings cell " , it doesnt add up. I'm guessing its because of the time not being a whole number? Cant seem to get over this hump ! And Im an excell newb.
sorry if its been asked and answered already
Thanks !
Ask me anything about Microsoft Excel Quote

      
m