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
ADVERTISEMENT
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
View Related
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
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
May 12, 2012
I have a table showing interest levels in training courses from a group of schools, eg:
English Maths Science
School1 3 4 2
School2 7 1 0
School3 3 2 5
I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.
I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:
=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)
But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.
I am sure Excel has the required functionality.
View 12 Replies
View Related
Aug 24, 2014
writing a formula to sum the six largest numbers from a row of numbers. I have used the array formula "=SUM(LARGE(E2:M2,{1,2,3,4,5,6}))" and it works for rows that have six or more numbers; however, not all of my rows always have six or more numbers. Rows that have less than six result in "#NUM!".
View 5 Replies
View Related
Nov 26, 2008
I need to find the largest value within a range that is less than the result of a formula from another set of values.
Something like this:
=MAX(E98:E115) that is <=(P63+((1.5*(P63-P61))))
View 3 Replies
View Related
Oct 14, 2008
See attached. This is a report that I have to do every month. I cant work out a formula for it. What I need is a formula that will pick out which is the largest Figure of column A,B,C,D for each row and put the corresponding header in column G. i have manually put these in. Would anyone know a formula i could use for this.
View 3 Replies
View Related
May 19, 2009
I have a named range which refers to: =MATCH(LARGE($G$5:$G$68,ROW(G5)-4),G$5:G$68,0)+4. It is supposed to find the largest figure in the adjacent column, list its row in cell H5, then the second largest for cell H6 and so on until the end of the data. The data in column G is dynamic and some cells will contain "0" with certain sections of data thus why I need a formula and cannot just use sort each time.
My problem is that when I use the formula as a named range I get "#N/A" and when I evaluate the formula I will get the answer but in array brackets (e.g.{17}) before the final step turns it to "#N/A". The formula will work fine if inputted directly into the cell but as the same formula will be used over three columns and numerous rows I would much prefer to use a named range.
View 8 Replies
View Related
May 15, 2014
I have data one sheet which is
Date Ref No Type %complete
Monday 1 Erect 75
Monday 2 Erect 50
Tuesday 1 Erect 95
Wednesday 1 Erect 100
On the next tab I am consolidating all the reference numbers and want to capture the latest % complete
I have been trying =Max(if ref no=1 and type = Erect, % complete),1
But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.
View 1 Replies
View Related
Dec 4, 2013
I'm logging failed password attempts and need to find the latest time logged for a single user (when user has failed 5 times).
So the 5th attempt, I need that time
So the usual - Application.UserName in the next free cell in a range and time stamp in the next cell.
What I've tried is:
Say log range is A1:B500 and
A=Username, B=Time stamp
In column C I've nested the two together
(this has more code but not required to get this working)
In C1
Code:
=A1&" "&"live"&" "&B1
So this will bring back "Username live 23:12:12". Now, say there is 5 of these (all with different times)
How do I find the latest time for that user?
Code:
IF(COUNTIF("USERNAME LIVE"&"*")=5,[find the latest time in that list",""
Just to wind you up some more, other users name will be logged and muddled up so it's literally -
"Excel - if a user has failed 5 times then give me the time of the 5th attempt"
I've tried a mixture of COUNTIF, MAX, LARGE, TIMEVALUE(...)
View 4 Replies
View Related
Nov 10, 2009
INDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.
View 5 Replies
View Related
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
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.
=index(sheetsize,match(A31,sheetgauge,0))
View 2 Replies
View Related
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
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
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
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
Feb 12, 2012
I've made a spreadsheet that allows the user too enter their clock in and out times in a day for a week, it allows you too fill in two weeks worth (because we get paid fortnightly). It automatically calculates each week's pay once it has been filled in, all I need is a formula too calculate how much they would be paid after tax. I can work how much they will be paid for the 2 weeks befor tax, but I just need a formula too deduct tax from this.
View 9 Replies
View Related
Sep 13, 2009
i need to creat a 12 month calculation of accounts
month 1 month 2 based on a precentage so i need to calulate the cell 1 pluse a precentage equals cell 2 and then cell 1 plus 2 and the precentage equils that
View 9 Replies
View Related
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
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
Feb 22, 2012
(Split cell into multiple rows) I need a macro to remove contents within brackets in a cell.
Example:
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
Oct 12, 2007
I have a worksheet containing square carriage return symbols (see below).
EXAMPLES
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
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
Range("C5:D9").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
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
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
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
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
May 6, 2014
I am trying to create a formula that calculates multiple commissions based on profit margin. So here is what I'm looking to. If the profit margin is between 50 and 70% than there is an additional 2% commission, if it's between 70.01-100% profit margin, than it's an additional 5% here is the equation I have=IF(OR(E2>50,E2<70),D2*2%,(IF(OR(E2>70.01,E2<100),D2*5%)))but it's still calculating at the 2% even thought it's an 86% margin.
View 4 Replies
View Related
