Nice, thank you.

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.

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?

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.

Data validation

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?

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?

Four numbers in the same cell 32 : 19 (15:9). How do I put each number in a different cell.?

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

http://www.techrepublic.com/article/...ctions/1033367

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)

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.

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

Delete both the 'Var' and see if it works.

I did that and then I had to enable macros and it worked. Thanks CrazyEyez. This thread is the greatest.

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.

[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

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

Thanks!

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:

A	B
2	9
20	30
60	70
90	110

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?

Josem,

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

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

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:

Then use:

=inRange(A2,$H$2:$I$5)

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

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.

Sorry if this has been asked already, is it possible to run a pivot table and chart off the same filters?

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 !