Finding Whether A Number Is A Square, Or Whether Square Root Is Integer

Aug 19, 2009

I need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger. So far Iv been doing

A1 = 2
A2 = AND(RIGHT(A1,1)=INT(A1))

True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have 49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND).

View 3 Replies


Square Root With Userform Textbox's

Oct 2, 2006

i am trying to use the square root formula to work out the top length of the picture on the userform from the numbers i have in the textbox's.

It is basically a 10x5 rectangle and i need to find the diagonal length

which should be 11.18 but it is returning 10

View 5 Replies View Related

Square Root Spiral Function Graph...

Nov 10, 2006

Square Root Spiral Function Graph. i was looking for vba code to graph the Square Root Spiral from Pythagorus in Excel...?

View 8 Replies View Related

Module Function (square Root Always To Be Positive So I Get A Result)

Dec 7, 2009

I have a formula and in it is a square root, i want the formula inside the square root always to be positive so i get a result.




Now i only get a result from the last two, but i want to use the modulus function so that instead of a maths error i get 1 for the first one, how do i do this?

View 2 Replies View Related

Punnet Square

Aug 15, 2006

I am doing a cross between two parents, whose possible genotypes are AA, BB, or AB.
I want to use if clauses to look at the parent genotypes, (paternal is listed in column E and maternal is listed in column F) and then give me the possible recombinant genotypes in column G. there are 9 possible combinations (recombinants in parenthesis)

AA x AA (AA)
BB x BB (BB)
AA x AB (AA, AB)
AB x AA (AA, AB)
BB x AB (BB, AB)
AB x BB (BB, AB)
AB x AB (AA, AB, BB)
AA x BB (AB)
BB x AA (AB)

I tried using if clauses, but i don't think i can nest more than 7 cases, and I have 9 different combos.

View 4 Replies View Related

Index - All Up All Square Feet

Mar 13, 2014

I have 6 rows A1-A6(sheetsize), they can be 1 of 8 numbers. (16,14,12,10, 5/8, 1/2, 3/8, 3/16) these are all metal gauges. The cell next to them B1-B6(sheetgauge) has a number that refers to how much sq ft each of these sheet metal sizes has. I would like a cell below A30 to add up all the sq ft in B1-B6 if it is 16 gauge. Then A31 will add up all the 14 gauge, etc.

How is this possible without having a huge formula.

Here is my standard formula, but it wont add the B cells together if more than one of the same sheet sizes.


View 2 Replies View Related

Use Of Square Brackets In Vba 2003

Jan 8, 2010

My code works great on my computer but when I bring it to another computer it gives me errors. I think it has to do with my use of square brackets to reference cells. the error that comes up is

compile error:
can't find project or library

What I'm doin here is going to my template sheet then getting the info from different cells and then later using it somewhere else. in place of using range and selecting cell I am using square brackets to select a cell. it worked fine on my computer and was a lot less writing, here is a small part of my code

View 9 Replies View Related

Square Brackets - Vba Notation

Mar 22, 2007

I often see square brackets around ranges in code. eg [A1]

if there are any advantages/disadvantages with using this notation, apart from the obvious its shorter?

View 9 Replies View Related

Address Separated By TWO Square Characters

Feb 15, 2009

An address is separated by the square characters, and usually I would use Text to columns>Delimited>Other>Alt+0010 to separate the lines. When I get to the Delimited screen where you check Other and enter Alt+0010, the keyboard just makes beeping noises and the text preview only shows the part of the address prior to the soft return, as if there is no char(10) delimitation.

Sheet1 I11299 S Taylor St. #12
Shakopee, MN 553792880 Tuva Rd.
Cokato, MN 5532131381 360th Ave
Siren, WI 5487245824 Dupoun Ave. N
Brooklyn Center, MN 554305718 Garden Lane #2
Shakopee, MN 553796583 Hall Ave
St. Paul, MN 5510771615 South 4th St. #2003
Minneapolis, MN 5545481525 6th Ave S #8
St. Cloud, MN 563019715 S Glen DR
Bloomington, MN 5542010340 2nd Ave. S. #344
Waite Park, MN 56387 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Formula To Convert Inches To Square Foot

Dec 30, 2008

I need a formula to automatically convert inches to square feet. I have =IF(G5>12,G5/144). and G5 is the cell used to enter your inch value. The formula wrks great, but only if you enter over 12 inches. I'm pretty sure Im on the right track, just need to know how to add in the part about if its less than 12 inches it should be multiplied by 12.

View 5 Replies View Related

Extract Numbers For Square Footage Calculation

Jul 14, 2009

I'm a new member to the forum and have a question about extracting numbers from a string for a square footage calculation. I am trying to extract the two numbers of varying length on either side of an "x" within an alphanumeric string. As you can see from below, the only constant is that each string will contain an "x" (assume there will be no other "x"'s in the string). I am trying to achieve the following:

A1 = 36.5 x 112 --> B1 = 36.5, C1 = 112
A2 = 36.5x112 --> B2 = 36.5, C2 = 112
A3 = 36.5' x112 --> B3 = 36.5, C3 = 112
A4= 36.5'x112.5' --> B4 = 36.5, C4 = 112.5
A5= abc123 36 x 112.5' --> B5 = 36, C5 = 112.5

If there is a non-VBA code fix, that would be preferable (mix of MID,FIND,MATCH functions...?), but I am okay with some basic VBA.

View 4 Replies View Related

VBA Square Option Buttons With Check Marks?

Feb 4, 2014

I try to make option buttons instead of round with a black dot but square box with check mark liked the check box. Box can be filled with color (light blue or other color). When the box is picked the fill change to white background.

I have 5 option boxs, I like to group them in one group because I have other groups using the option boxs. I want the check mak stayed in that group when it is picked.

View 14 Replies View Related

Formula For Calculating Largest Square To Fit In A Quadrilateral

Feb 23, 2010

I know this ain't going to be easy if at all do-able, but would anyone know if there can be a formula/s made to calculate the largest size "square" shape (e.g. all 4 corners at 90 deg.) that would fit in to a known size Quadrilateral?

Value A,B,C,D and Z are all known values as are the diagonal lengths between corners

I failed dismally in Geometry at High school, but how was I to know that 35 yrs later I would need to know this stuff!!

Please see diagram for an idea of what I'm trying to achieve.

View 10 Replies View Related

Macro For Removing Contents Within Square Brackets

Feb 22, 2012

(Split cell into multiple rows) I need a macro to remove contents within brackets in a cell.

A1 contains
[something, separated; a, couple of times; like, this] With, some; other, text

The result should be:
With, some; other, text

I want the macro to remove the brackets and everything within it for all marked cells. (I do not want to use search and replace function since I got a lot of these data and needs to repeat this procedure).

View 9 Replies View Related

How To Remove Square Carriage Returns From My Cells

Oct 12, 2007

I have a worksheet containing square carriage return symbols (see below).

Eg1. Baked[]Beans on []Toast
Eg2. Smoked[][]Salmon in Brine
Eg3. Ice[][][] Cream [][]Cosmopolitan
Eg4. Mixed[]Nuts[][]per kilo[][][][]
Eg5. [][]Baby Shampoo[]Fragrance Free

This data was extracted from an SQL Database and dumped into Excel.

I would like to know if someone can suggest a script to replace all occurrences of [] with a space.

Once I perform this, I can then perform a Search/Replace function as follows:

Replace 4 spaces with 1 Space
Replace 3 spaces with 1 Space
Replace 2 spaces with 1 Space

Hopefully then, the final result will appear as follows:

Eg1. Baked Beans on Toast
Eg2. Smoked Salmon in Brine
Eg3. Ice Cream Cosmopolitan
Eg4. Mixed Nuts per kilo
Eg5. Baby Shampoo Fragrance Free

Eg5. is a little tricky because a space will appear at the beginning of the cell (see above). It would be good if there's a script to remove occurrences of this also.

View 9 Replies View Related

Selecting Diagonally, Square-shaped Selection

Jun 5, 2008

I am trying to select the cells in a column and then all of the adjacent cells to the right.

If I record a macro, it looks like this:

Range(Selection, Selection.End(xlUp)).Select

How can I turn those static cells into volatile cells (I think that's what you call it)?

I basically want to select from the active cell (C9) to the beginning of the column (but leave out of the selection the header row) and then the adjacent cells to the right (D5:D9), but not with fixed cell references.

View 9 Replies View Related

Getting Max Value Of A Point Inside A Square Defined With X,y Coordinates

Apr 18, 2006

I have a square which is defined with x,y coordinates (roughly 2000 points), and for each point I have a value. And I have to divide this square in to 9 equal sub grids, and find the max value in each sub grid. I have all the coordinates for the sub grids. But I cant get the max value of the specified cells.

I am thinking something like this:

if 0<x>10 and 0<y<10 then it is sub grid 1 and I want to find the max value there
and if 0<x>10 and 10<y<20 then it is sub grid 2 and so one..

View 9 Replies View Related

Multi-line Text Box Square Removal

May 2, 2007

I have a multi-line text box in a user form. This information is loaded onto an excel spreadsheet. Where I have pressed the return button to go to the next line in the text box it loads in a square (actually I think it's probably more of a rectangle) character onto the excel spreadsheet. It there a way to get rid of this character?

View 3 Replies View Related

Square Character Shows In Cell From TextBox

Jun 7, 2007

I am using a User Form to input data to a spreadsheet and have an issue. I've set the TextBox WordWrap & MultiLine Property to True and the textbox values don't show the reverse P character, but how do I get the "square" from populating into the cells on my worksheet? I was using this old thread as a point of reference, but didn't understand how to use or where to put it in my form. I'm referring to the code that Dave Hawley supplied. Strange Characters Pasting to Textbox

View 5 Replies View Related

Drop Down List :: (the Arrow In The Square) Does Not Show Up Unless I Click On The Cell

Apr 17, 2007

I have created a drop down list but notice that the button (the arrow in the square) does not show up unless I click on the cell. Is there a way to show the button at all times so the user knows a drop down list is available?

View 9 Replies View Related

Averages Formula- Spreadsheet Which Has A Column Which Shows Square Footages

Nov 3, 2008

I am working on a spreadsheet which has a column which shows square footages (Column AE) and I also have a column which shows costs (Column Y).

What I would like to do is to bracket these footages in to 6 bands (0 - 5000, 5000 - 10000, 10000 - 15000, 15000 - 20000, 20000 - 25000, 25000+) and then average the costs within a particular band.

View 9 Replies View Related

Replace Small Square Character With Line Break After Import

Nov 18, 2008

When importing text from a CSV, the 'line returns' appear in a cell as a small square with faded left and right sides. The do force a line break as intended. However, they are a nuisance in reading and printing.

When I use ALT+ENTER to force a line break, there is no visible character (on screen or printed) for the line break, which is as it should be.

How can I replace this 'special character' line break with one not visible?

View 9 Replies View Related

Conditional Square Of Pearson Product Moment Correlation Coefficient

Jan 3, 2010

I have a very large spreadsheet, a few columns and rows I attach..

in col a we have the time of the column b i have a numerical rank for the
importance of factor 1,in column c i have similiar for factor 2.

What I want is the correlation for each event between col b and col c using the rsq function..i have no problem using this function..but i need the result for each event produced in col d in this same sheet and printed on each row and since I have thousand of rows i need a formula I can fill down

see attached sheet

View 5 Replies View Related

Make Cell Real "Square" In Size

Mar 22, 2007

I found the width and height of Excel does not make sense at all.

For example, default Width is 8.38 while height is 14.25
But the width of the cells on screen and print out is much longer than the height in length.

How to make all cells in the whole spreadsheet real squares ?

I tried changing width and height to the same number but it does not work....

View 9 Replies View Related

CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

Jan 8, 2008

I have a cell that is acutally a concatination of several other cells. The formula is something like this....

=A1 & B1 & A1 & C1 & A1 & D1 & A1

Cell A1 contains the formula =char(13). This forces a line break between each data element.

My problem is that on some peoples machines the cell displays with a square at the end. I'm not sure why it does this only on some machines. Is there a better way to do this or is there a way to suppress the square?

View 9 Replies View Related

Square Shows In Cell After TextBox Transfer To Cell

Jan 22, 2008

I have a Textbox on a Userform that allows users to enter text and code copies the text to a nominated cell on a sheet. My difficulty is that when the text is copied to the cell at the end of each line of text there is a small 'open square' symbol that I would prefer not to show. I can manually delete the symbol but would like it either not to appear of be able to automatically delete it. If I copy the text to a word file the symbols do not appear.

Private Sub CommandButton1_Click()
Sheets("Marketing").Range("b4") = UserForm1.TextBox1
End Sub

View 5 Replies View Related

Finding A Specific Integer In A String

Aug 3, 2009

I have a string in one of the following formats:

Banana 2, Orange 5, Lemon 0
Banana 7, orange/Lemon 9, cucumber 6
Melon/grape 3, Pineapple/ Orange 1
Banana 1, orangefruit/Lemon 2, pine 8

I would like to take out the first integer that comes after the word Orange (not case sensitive). I'm kinda at a loss here, how do I go about accomplishing this?

View 9 Replies View Related

Convert Integer Number To Column Alphabet?

May 8, 2014

I have M106 = 4

I need a simple formula to convert that into column Alphabet + 1

So 4 will be Column E (A,B,C,D and 4th will be D+1= E)

View 3 Replies View Related

Converting A Text To A Number Whose Type Is LONG Not Integer

Mar 11, 2007

I have a user form and to input the hourly wage i need to convert the text entry to a number whose type is LONG and not integer.

Does anyone know how to do this?

Or is there a way to input a number in a userform without a text box entry?

View 9 Replies View Related

Rounding Formula :: Round To The Nearest Integer (whole Number)

Feb 3, 2007

I would like to round this number (3.5) to become 4, but by using this formula =CEILING(A1,0.5) the result will be 3.5

i need a formula that turns 3.5 to 4

when i use this formula =ROUND(A2,0.5), 3.5 becomes 4, but 3.2 becomes 3 not 3.5

i.e. a formula turns 3.2 to 3.5 and 3.5 to 4

View 6 Replies View Related

Copyrights 2005-15, All rights reserved