Over 1 million tech questions and answers.

Solved: Excel Functions Attn: Firefytr & Bomb

Q: Solved: Excel Functions Attn: Firefytr & Bomb

OBP has solved my macro problems, and I now have my client's customer database in an Excel spreadsheet of 11,000 rows. But, I'm having trouble making common math functions like "sumif" and "if" work right to analyze the data. OBP recommends either of you as strong in functions. Could you pls e-mail me so that I can send you a real spreadsheet to figure out why the functions aren't working? Thx. Bob

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel Functions Attn: Firefytr & Bomb

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel Functions Attn: Firefytr & Bomb

Read other 12 answers
RELEVANCY SCORE 68.4

Hello, I discovered this forum when searching for a way to create a search macro to copy rows of data based on values in certain columns. The post resolved by bomb#21 has given me code that does 90% of what I need:
http://forums.techguy.org/business-applications/559825-solved-excel-search-macro-needed.html
Sub Search()
Range("E11").CurrentRegion.ClearContents: Range("E11") = "Results"
FindWhat = UCase(Range("E5"))
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Cell.Offset(, -1).Resize(, 3).Copy Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub

As an absolute newbie to macros (I've been using them for 3 days, and it took me an hour to figure out why I was getting errors when changing from column B to column A in the above code - for any other newbies it is the Cell.Offset (, -1) part of the code), I was wondering if it is possible to have this code in a Sheet2, to return data from Sheet1 (I had assumed it would be as easy as changing the code to <For Each Cell In Range("Sheet1!B2", Range("Sheet1B" & Rows.Count).End(xlUp))>) and whether it would be possible to have a search popup box instead of just entering text into a cell (E5 in the above code).

Apologies, I know that this is probably a very basic question, that I could learn with some excel training. Unfortunately this is a smalli... Read more

A:Solved: Excel search macro almost solved by an earlier bomb#21 post

Read other 9 answers
RELEVANCY SCORE 68.4

Hi there

I am a bit of a Macro novice, but found the below macro was exactly what I needed to search one tab by key words and display asscoiated rows containing that data in another tab.

The problem I have, is that it searches columns A to L, however, I need it search an extra column to M. I have tried all sorts but can't crack it as not an expert on deciphering the code. Can any PLEASE tell me what I need to tweak in the code to extend the search to column M?

Any help sincerely appreciated as I've tried everything!

Thanks
Sub test()
If ActiveSheet.Name <> "Query Directory" Then Exit Sub
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Sheets("Search results").Range("3:10000").Delete
SearchTerm = Application.InputBox("What are you looking for?")
Application.ScreenUpdating = False
Range("L1") = SearchTerm
Range("L2:L" & LastRow).FormulaR1C1 = _
"=IF(ISERR(SEARCH(R1C12,RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1])),0,1)"
If WorksheetFunction.CountIf(Columns(12), 1) = 0 Then
Columns(12).Delete
Application.ScreenUpdating = True
MsgBox "None found."
Else
For Each Cell In Range("A2:A" & LastRow)
If Cell.Offset(, 11) = 1 Then
Cell.Resize(, 11).Copy Sheets("Search Results").Range("A" & Rows.Count).End(xlUp).Offset(1)
x = x + 1
End If
Next Cell
Columns(... Read more

A:Excel search macro almost solved by an earlier bomb#21 post

Read other 16 answers
RELEVANCY SCORE 63.2

Hi,

how can I get the sheet name? i searched all the ecxel functions, no noe can get the sheet name?

thanks
 

A:Solved: Excel functions

Read other 6 answers
RELEVANCY SCORE 62.8

Please help!!!

In the attached file, I have (with help) figured out the majority of the formulas needed. There are 3, however, that I can not get.

Here is what I can not figure out:

Issue # 1: Disregard the notes in "account 2 on" tab. What I need to do is the following:
If the percentages of G8:AY8 are selected AND of G9:AY9 are selected, I need it to show in D12
90% 100% "Platinum"
80% 90% "Gold"
60% 70% "Silver"
Less than 60% And less than 70% "Not to standard"

Issue#2: In D49, I need Platinum to = 1, Gold = 2, Silver = 3 and Not to Standard = 4. I need whatever the lowest of the 4 is, or how the boss put it, the lowest common denominator.

Issue #3: On the master tab, there are spots for percentages. I need to know what percentage are Platinum, Gold, Silver and Not to Standard.

Any and all help will be greatly appreciated!!!

Here is a link to the file.

http://www.savefile.com/projects/808722413
 

A:Solved: Need help with complex excel functions

Read other 16 answers
RELEVANCY SCORE 62.8

Hi guys

I am trying to set up a markbook type thing using excel. File attached.
There are 10 sets of mark. Each set is made up of a academic mark (%), which will be typed in by the teacher. The second column is a symbol which will add or subtract bonus marks. A=25
B=15
C=7.5
D=0
E=-15

The teacher will just type in the symbol.

The third column is a hidden column. This column uses an if function which gives the correct bonus mark as described above.

The fourth column must add the marks from the first column and the bonus marks in the third column.

At the end, there must be an average column, which must keep a running average of the marks.

My problem is this, if I use the sum function it returns a 0 value, if no values are entered. This then messes up the average value at the end. How can I enter the sum value so that if no figures are entered the total cell remains blank so that the correct average value is reflected.

Many thanks
Cara
 

A:Solved: Excel sum and average functions

Read other 16 answers
RELEVANCY SCORE 62.8

Hi there,

I have a problem with calling some functions in vba excel.

I have a workbook with a single worksheet. In VBA I have all my code in the Sheet1 object including this:
Code:

Sub OpenSetup()
Call CheckDate
If DateInRange = 1 Then
Call DeHighlightYesterday
Call HighlightToday

Else
MSGOPEN = MsgBox("A new week has started. Do you want to update the calender?", vbYesNo)
If MSGOPEN = vbYes Then
Call XFRCLEAR
Else
MsgBox "OK, please click ""Update Calender"" when your ready."
End If
End If
Range("D8").Activate
MsgBox "Welcome, enjoy your stay"
End Sub

Then under the ThisWorkbook object I have...
Code:

Sub Workbook_Open()
Call OpenSetup
End Sub

...intending to run the OpenSetup function when the workbook is opened. However when I step through to make sure it works I get this error message:

"Compile Error: Sub or Function not defined"

I am sure I am missing something very simple but I am just beginning with VBA and have drawn a blank.
 

A:Solved: VBA Excel calling functions

hmm, should I have all my code under a module rather than the worksheet object?
 

Read other 3 answers
RELEVANCY SCORE 62

Is it possible to write a nested formula such that the result of the first calculation is an array that is used in the 2nd formula?

I have a formula:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)​
That is nested in an index formula, INDEX(array,row_num,column_num), to result in:

INDEX(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),row_num,column_num)​
I want the result of the Vlookup to be a range name or an array that the Index formula will accept. How do I do this?

I'm using Excel 2003.
 

A:Solved: Excel question re nested functions

I want the result of the Vlookup to be a range name or an array that the Index formula will accept. How do I do this?
Click to expand...

AFAIK VLOOKUP will not be able to return a range; instead, perhaps you could solve the problem by devising a way to return the top left and botton right cells of your target range - then you can combine using INDIRECT?
 

Read other 2 answers
RELEVANCY SCORE 62

Hi,

I have query in access that i output in excel and want to graph the output. I have written the code for this in access -> visual basic

i want the code to get the range of the xvalues and the yvalues from the output query results in excel sheet and graph column B vs Column A...
For the graphing part of the code this is what i have:

' Create a new chart.
Set xlChartObj = xlApp.Charts.Add
With xlChartObj

' Specify chart type as 3D.
.ChartType = xlLineMarkers ' Set the range of the chart.
.SetSourceData Source:=xlSourceRange

'_____________________________________________________

.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = HOW DO I GET THE RANGE OF THE XVALUES thats in column A of the excel sheet????
.SeriesCollection(1).Values = HOW DO I GET THE Y values that is column B???
'_________________________________________________________
' Specify that the chart is located on a new sheet.
.Location Where:=xlLocationAsNewSheet
ANY help will be greatly appreciated!!

Thanks
 

A:Solved: Calling excel functions from access

Read other 6 answers
RELEVANCY SCORE 61.2

Hello,

I have tried seaching for this information, but I can't find it anywhere.
What I would like to know is, do function words in Excel convert automatically if sent to someone with Excel in another language.
For example:

I am user English version of Excel. In my spreadsheet I use "COUNT" funtion. If I then send this document to someone who has the French version of Excel does "COUNT" then become "COMPTE" (which is the French equivalent).

Thanks in advance
 

A:Solved: Converting excel functions from English to French

Read other 6 answers
RELEVANCY SCORE 58

I Somehow managed to get some sort of Archive bomb/zip bomb on my alienware m15x windows 7 64 bit laptop, i had 70Gb free next thing i know bluescreen shutdown reboot with 20Mb i scanned with AVG found an archive bomb and trojan, trojan was successfully quarantined and archive bomb wasn't so it was attached to the handbrake Gui, deleted that, thought it was all gone and gamed again and it bluescreen shuts down, so i tried looking for solutions on the internet (after i rescanned with AVG and found nothing) and i downloaded Malwarebytes that found 116 infections and successfully removed them, but it was still eating memory, i tried working Hijack this but couldn't tell friend from foe so gave up with that, then i installed Combofix, which seemed to me to have fixed the problem after running and restarting 70Gb back again, but then i gamed a bit more and it bluescreen shut down again and 20Mb free space and continuos warnings of low disk space, tried Combofix again but no change, all of my restore point seem to have gone and i can't find a solution, please can someone help me soon it seems to be rendering my laptop useless.
 

A:Archive Bomb/Zip Bomb? Memory eating

Read other 10 answers
RELEVANCY SCORE 53.2

I need some help in trying to create a formula that solves the following problem that has 3 variables:

IF A1 is less that 30 then multipy by 2, IF A1 is greater than 30 but less than 60 then multiply by 3, IF A1 is greater than 60 multiply by 4.

Any suggestions would be greatly appreciated.
 

A:Excel IF functions

Read other 8 answers
RELEVANCY SCORE 53.2

Hi:

I have several columns that look like this:

10/28/2002 07:55_AM
10/28/2002 02:50_PM

(both) and

10/28/2002 07:55_AM
now I need a function that can delete the dates only and leave the time.

Is this possible?
 

A:excel functions

Read other 16 answers
RELEVANCY SCORE 53.2

Hi,

I'm trying to calculate angles using Tan on excel based on data that is plotted on an x and y co-ordinate graph. I've produced a formula that means I can calculate the angle whereever the co-ordinates fall. I've tried this formula in excel and first it said I have too many arguments, I thnk you're only allowed 3 IF's? And then when I edited it, that it contains and error.

=IF((AND(xcell>100,ycell>100), ((180-45)-(ABS(360-(DEGREES(ATAN(y-100/x-100)))))))),IF(AND(xcell>100,ycell<100),((180-45)-(ABS(DEGREES(ATAN(y-100/x-100))))))),IF(AND(xcell<100,ycell<100),((180-45)-(ABS(180-(DEGREES(ATAN(y-100/x100)))))))),IF((AND(xcell<100,ycell>100),((180-45)-(ABS(180+(DEGREES(ATAN(y-100/x-100)))))))))

and when edited...

IF(AND(G12>100,H12>100),(180-45)-(ABS(360-(DEGREES(ATAN((H12-100)/(G12-100)))))),IF(AND(G12>100,H12<100),(180-45)-(ABS(DEGREES(ATAN((H12-100)/(G12-100)))))),(IF(AND(G12<100,H12<100),((180-45)-(ABS(180-(DEGREES(ATAN((H12-100)/(G12-100)))))),(AND(G12<100,H12>100),((180-45)-(ABS(180+(DEGREES(ATAN((H12-100)/(G12-100))))))))))))

Is there anything you can see in the formula that could be causing the problem?

Thanks guys.
 

A:IF functions on excel

Read other 10 answers
RELEVANCY SCORE 53.2

Hi. can someone help me simplify this formula?

=IF(OR(NOT($L4=""),NOT($N4="")),HLOOKUP($H4&"-2",raw_Test1.1!$C$2:$EE$364,MATCH($D4,raw_Test1.1!$B$2:$B$399,0),0),"")
 

A:Functions in Excel

cleo123 said:


Hi. can someone help me simplify this formula?

=IF(OR(NOT($L4=""),NOT($N4="")),HLOOKUP($H4&"-2",raw_Test1.1!$C$2:$EE$364,MATCH($D4,raw_Test1.1!$B$2:$B$399,0),0),"")Click to expand...

This seems pretty much the simplest solution. I can think of only two additions.

1)
You could define some named ranges, like
SearchTable = raw_Test1.1!$C$2:$EE$364
HeaderRow = raw_Test1.1!$B$2:$B$399

2)
contract the OR like this
$L4 & $N4 <> ""

Result:
=IF($L4 & $N4 <> "",HLOOKUP($H4&"-2",SearchTable,MATCH($D4,HeaderRow ,0),0),"")

This formula isn't simpler or faster, but maybe it's easier to read.

Jimmy
 

Read other 3 answers
RELEVANCY SCORE 53.2

I have a large list of values and I need to find the number of values that have a absolute difference of 3 from the value above them in the list. I do not know which fuction to use. Please help.
 

A:Excel functions

Read other 7 answers
RELEVANCY SCORE 53.2

I would like to know if there is a way for excel to search a section or entire worksheet for amounts which add up to a specific amount.

For example, search worksheet to find amounts which total to 26,232.

I am using Excel 97 SR-2.

My life sure would be easier if excel can do this!!!
 

A:excel functions

Read other 7 answers
RELEVANCY SCORE 53.2

I have windows XP. I am in excel, I have to put a function in. I thought I had it but it is not working. What I want it to do is this....

Week 1

361.00

*
*
*
*
Total

pretend this is an excel spreadsheet, under Week where it has a one, this changes each week.( 1, 2, 3, 4, 5 ) for the week number of the month. On the first week I want (*) to go to 361.00. Are you with me? This is the function I used, =IF(E$3=1,"",F$7). You will have to take my word for the 1to be at E3 and the 361.00 to be at F7. When I do this function I am at the first *. If the week number is at 2,3,4,5 I want it to show nothing in those boxes. I hope I am making sense. Can someone help!!
 

A:Functions in excel- Help?

Read other 7 answers
RELEVANCY SCORE 52.8

Well, that's the title, but I'm not even sure that it's the best way of doing what I want to do.

This is a basic question and I should know the answer, but none of the combinations of functions that I use seem to do the job as I would expect it.

Basically I have a spreadsheet with a number of columns in it, three of which are year, month and GWP. I need to put these figures into a table (and I've been told I can't use a pivot table, even though it would seem the ideal solution!), to calculate GWP by month and year. I've created named ranges called Month, Year and GWP, and I want something along these lines -

if(And(month=2)(year=2009),sum(GWP))

but that just returns "True" when I was expecting, well, a sum of GWP.

Can anyone help! Do I need to use an array formula, and if so, what is it!?

Thanks for any assitance.

Ed
 

A:Nested if functions in excel

Read other 6 answers
RELEVANCY SCORE 52.8

Hi..I have just purchase HP Notebook_14am118TX.. and trying to understand the functions in this laptop.. I use excel a lot and unable to use the keyboard functions in this laptop... can you tell me how do i delete the entire row in the excel using the keyboard...there is no application key.. also if i have to open one cell i have to use Function key along with F2..is there any alternative.. how do i use the right click here for paste special.. please help its urgent   

Read other answers
RELEVANCY SCORE 52.8

Hi everyone ... first post here.

I have a fairly complicated Excel workbook which was getting difficult to maintain so I changed a lot of cell-based calculations to VBA functions. The VBA functions are easy to read, can be commented and use global variables for defining cell, row, column and worksheet locations. Big improvement.

But - it is now so slow it's painful. I can watch the status bar grinding through the progress towards "Calculating cells: 100%". When I run in debug mode with breakpoints, I notice that some of the functions seem to be executing twice. For example I have one function that calculates values for 4 cells and it executes 8 times; I cannot see why this might happen for the life of me. Sometimes the arguments to the function are empty when they clearly should not be. So, I have a few direct questions:

1. Any ideas why functions might calculate twice?
2. Are there any ways of monitoring the way a workbook calculates cells so that I can follow the 'program flow'? (Just trying to step through using Shift+F8 would take far too long)
3. Any suggestions for tools that might help me to analyse the way in which the calculation speed could be improved?

For reasons of compatibility I have to be working with Office '97. I have tried switching calculation mode to manual in Tools|Options, but the client just complains all the time that nothing works. And doesn't seem to be able to learn to use F9.

OS: Windows XP SP3 + updates

T... Read more

A:Calculating VBA functions twice in Excel

Read other 16 answers
RELEVANCY SCORE 52.8

First off I'm new to the forum, and just wanted to let you all know that everthing I know about Excel I tought my self. So, that being said I need help with mixing functions and formulas. What I want to do is be able to type a number in to a cell then in the adjacent cell have the number divided. But the problem that I've run into is I need this number to be rounded down. I'm not sure how to add the rounddown function with my existing formula. Any help will be appreciated.

Justin
 

A:Excel Help: Functions and formulas

Read other 13 answers
RELEVANCY SCORE 52.8

Hello, anyone know any math function formulas in Excel? I need to determine the various combinations of cubic size in inches, of which would equate, when multiplied (length X width X height), less than 5,184?

So for example length 10 width 20 height 20 equals 4,000 which is less than 5,184. That's a good combo. I'd like to be able to obtain all possible combinations of numbers that would fall be less than 5,184.
Any help is greatly appreciated.
Thanks in advance.
 

A:Math functions in Excel?

I don't know what you need but have you checkd the math functions in Excel?
BTW, you forgot to mention the Excel version you're using
I found attached file while seraching for other things, maybe it will help you on the way.
 

Read other 2 answers
RELEVANCY SCORE 52.8

When I enter the function - =if(c2=>0,C2) if the cell C2 is empty I want the cell that the function is in to be empty however it shows a 0 value. Also if I was to enter the word into the cell C2 it will display the text even though its not > 0 or = to 0. It seems that excel treats any value as being greater than or = to 0.

Help, has anyone got any ideas of a way around this??????
 

A:IF Functions in Microsoft Excel

It rather depends on what you want to see in all circumstances, but this might do it for you ..

=IF(AND(ISNUMBER(C2),C2>=0),C2)
 

Read other 2 answers
RELEVANCY SCORE 52.8

Hi there,

Im working on a naming data base. Its an excel spread sheet where I name pitches from baseball pitch fx data. Without going into details about my database, as what i do with it isnt important, I am trying to write an if statement that will give me the ability to ask if a pitch is located on the x axis between a certain point and is locted between 2 points on the y axis and is this speed/type than name it this. so example

if "pitch A" is between 7 and 12 on the x axis and between 2 and 10 on the y and is a FF (4seam fastball) than return "Fastball" in cell C2 but if its not than return "-" Any help or insight would be great.
 

A:Trying to properly use If with And, Or functions in excel

Read other 7 answers
RELEVANCY SCORE 52.8

hi:

How are you?

I need to protect some information in excel worksheet where no one can see except me.

e.g the names columns. First names and Last names (2 columns)

I know i can protect the entire worksheet, where the contents of the first names andlast names will be visible , but can't be changed and also i can hide those columns where no one can see them and protect the worksheet. The problem is if i copy the entire worksheet and paste it in a new file, then they can be unhid.

What I need is just to protect two columns where no one, I mean no one except me can have access to them, be it through copy and paste or unhiding, Is this possible in excel?
 

A:Excel Functions - urgent

Read other 16 answers
RELEVANCY SCORE 52.8

Can I create a formula in excel to check when a file was last modified and have it return a variable based on that?

Something like:
=if(FileDateTime("C:\Documents and Settings\853\Desktop\a.txt") = Today, Yes, No)

Or am I way off?

Any help please would be great.
 

Read other answers
RELEVANCY SCORE 52.8

For an example, I am looking to get D7 on the 'WS' worksheet to look through both column B and C of the 'AJE' worksheet and find anywhere there is the label of the value of A7(from WS!), and then sum the values of column D - column E on AJE! for only those corresponding rows

I then want to carry this down for each cell of column D & E on 'WS'

I'm not sure if this is something fairly easy, or not. I do know some VBA (if I have to go that route) but haven't done much within excel: so even how to get started would be great.

Thanks!
 

A:Excel 2007: possible VBA, or can I use functions?

I'm sure I don't understand the task, but here's a formula into D7:

Code:
=SUMIF(AJE!B:B,WS!$A7,AJE!D:D)+SUMIF(AJE!C:C,WS!$A7,AJE!E:E)
Jimmy
 

Read other 2 answers
RELEVANCY SCORE 52.8

In Excel 2007, I'm trying to do the following:

If I enter the word "Fox" in A1, then the value of "$100.00" entered into B1 will be copied to the "Fox" Tab in the same workbook in cell C6. Any help would be appreciated.
 

A:Excel 2007 Functions

Read other 7 answers
RELEVANCY SCORE 52.8

Does anyone know where I can find a printable list of the functions in Excel with examples of what they do and how to use them? Thanks.
 

A:List of Excel Functions

http://www.contextures.com/functions.html

Rgds,
Andy
 

Read other 3 answers
RELEVANCY SCORE 52

Hi im trying to allocate costs for a large number of customers where they are charged by lot size. here is some of sq.ft data and rate data from the Sheet2 tab

Sq. Ft.
7560
7200
14842
11277
7216
8860
15211
16506
30999
23723
7626
20915
10530

Sq. Ft.Fixed
<10000 $3.15
<15000 $3.20
<20000 $4.26
<25000 $4.33
<30000 $5.38
<35000 $5.42
<40000 $6.48

so 9000 sq.ft. returns $3.15, 23000 sq.ft. returns $4.33 etc...

I have the list in a seperate tab with everyones given square footage. I have used the IF AND function to determine if that customer is in one particular range, say between 10,000 and 15,000, but i am lost at how to combine formulas so that excel can choose which range the given customer is in so that it can return the correct fixed amount.

these are my functions, but how do i combine these so that it can be in one column instead of required 7 different columns for every sq.ft. range and rate formula?

=IF(D3<Sheet2!$A$2,Sheet2!$B$2,0)
=IF(AND(D3>Sheet2!$A$2,D3<Sheet2!$A$3),Sheet2!$B$3)
=IF(AND(D3>Sheet2!$A$3,D3<Sheet2!$A$4),Sheet2!$B$4)
=IF(AND(D3>Sheet2!$A$4,D3<Sheet2!$A$5),Sheet2!$B$5)
=IF(AND(D3>Sheet2!$A$5,D3<Sheet2!$A$6),Sheet2!$B$6)
=IF(AND(D3>Sheet2!$A$6,D3<Sheet2!$A$7),Sheet2!$B$7)
=IF(AND(D3>Sheet2!$A$7,D3<Sheet2!$A$8),Sheet2!$B$8)
 

A:excel 2003 IF AND functions choose?

Hi CaliMac10, and welcome to TSG.

You might want to use the VLOOKUP function in Excel to do what you want.
 

Read other 2 answers
RELEVANCY SCORE 52

I am trying to perform a calculation if the value of the IF-THEN function is true...

=IF(A2=yes,B2*0.2,"") - - I tried it as: =IF(E2=yes,"F2*0.2","") also, but then my cell is not referenced anymore.

Result: #NAME?

Am I doing something wrong- or asking Excel to do something it can't?

Thanks!
 

A:Microsoft Excel - If/Then Functions with Calculation

Welcome to the forums. You need quotes around the yes since it is looking for text.
 

Read other 1 answers
RELEVANCY SCORE 52

I want to know fi there is a formula or function which can determine the most number of times a value appears within a range of cells. For example, cells a1 thru to g10 contain a variety of numerical values, some more than others. Now I want excel to tell me in cell a12 the number that appears most within the range a1 thru to g10. I also want excel to tell me in cell b12, the second most common number that appeared within the range. And so on...

Is there such a formula?

I look forward to your responses.
 

A:excel 2003 formulas and functions

Read other 8 answers
RELEVANCY SCORE 52

I want to know fi there is a formula or function which can determine the most number of times a value appears within a range of cells. For example, cells a1 thru to g10 contain a variety of numerical values, some more than others. Now I want excel to tell me in cell a12 the number that appears most within the range a1 thru to g10. I also want excel to tell me in cell b12, the second most common number that appeared within the range. And so on...

Is there such a formula?

I look forward to your responses.
 

A:excel 2003 formulas and functions

=mode(range)
should give you that info

so in A12 type
=mode( a1:g10)
 

Read other 3 answers
RELEVANCY SCORE 52

I have a working formula, it tests if cell F4 is equal to a range of other cells.
I need it to test both E4 OR F4 and I just can't figure out how to get it to work and I'd really appreciate some advice? The formula that works for just F4 is:
=IF(ROUND(F4,2)=ROUND((G4-H4-I4+J4-SUM(K4:AG4)),2),"OK","Oops")
 

A:Excel nested Round with OR functions

Here you go, should work (posting from phone) ...
=IF(OR(ROUND(E4,2)=ROUND((G4-H4-I4+J4-SUM(K4:AG4)),2),ROUND(F4,2)=ROUND((G4-H4-I4+J4-SUM(K4:AG4)),2)),"OK","Oops")
 

Read other 3 answers
RELEVANCY SCORE 52

I have 2 sheets on my workbook what I want to do is compare (Sheet2)C = (Sheet3)V and if the result is true then copy (Sheet2)D text into (Sheet3)W.

=IF(V2=Sheet3!C2, Sheet3!D2, "") but the result I had is always FALSE or ""



Columns C and V are numbers

Sheet2
C D

0001 Computadoras de Escritorio
0001 Computadoras de Escritorio
0004 Workstations
0004 Workstations
0005 Servidores para computadora
Sheet3
V W

0001
0001
0001
0004
0005

I don't know how to solve this so any help will be appreciated.

thank you in advance
 

Read other answers
RELEVANCY SCORE 51.6

Greetings,

While surfing the net I all of a sudden started getting notification from my McAfee that some viruses oh PUPs had been deleted. it had asked me if I wanted to scan the system I chose yes. It wouldn't scan and my IE wouldn't load. I couldn't get online with AOL it would close as soon as it connected.

To make a long story of disaster short the only thing I could do was system restore to an earlier time today. Everything is back to normal and now I'm doing a full system virus scan.

Attached is a HJ log would someone mind reviewing it for me.

Smitty

Logfile of HijackThis v1.99.1
Scan saved at 8:41:17 PM, on 5/25/2006
Platform: Windows XP SP2 (WinNT 5.01.2600)
MSIE: Internet Explorer v7.00 (7.00.5346.0005)

Running processes:
C:\WINDOWS\System32\smss.exe
C:\WINDOWS\system32\winlogon.exe
C:\WINDOWS\system32\services.exe
C:\WINDOWS\system32\lsass.exe
C:\WINDOWS\system32\svchost.exe
C:\Program Files\Windows Defender\MsMpEng.exe
C:\WINDOWS\System32\svchost.exe
C:\WINDOWS\system32\spoolsv.exe
C:\Program Files\Common Files\Acronis\Schedule2\schedul2.exe
C:\Program Files\Common Files\AOL\ACS\AOLAcsd.exe
C:\Program Files\Common Files\AOL\TopSpeed\2.0\aoltsmon.exe
C:\WINDOWS\system32\Ctsvccda.exe
c:\program files\mcafee.com\agent\mcdetect.exe
c:\PROGRA~1\mcafee.com\vso\mcshield.exe
c:\PROGRA~1\mcafee.com\agent\mctskshd.exe
C:\WINDOWS\Explorer.EXE
C:\Program Files\Common Files\Microsoft Shared\VS7DEBUG\MDM.EXE
C:\Program Files\Photodex\CompuPicPro\Scsi... Read more

A:Solved: Got hit by a BOMB.. I think

Read other 6 answers
RELEVANCY SCORE 51.6

We recently upgraded to office 2003 from office 97. In excel 97 in the tools, options transition tab you were able to setup the transition navigation keys and use lotus 1-2-3 help. In Excel 2003 I see the setup the transition navigation keys but it doesn’t seem to work like it did in Excel 97. Like when I press the / key in 97 it gives you 1-2-3 help but doesn’t do it in Excel 2003. Is this not installed properly or this the way this functions in Excel 2003?
 

A:Lotus Keyboard functions in Excel 2003

The feature was removed in Xl2003.

James
 

Read other 1 answers
RELEVANCY SCORE 51.6

I have a compaq laptop AMD K6 3d 300 mhz, 32 meg ram, Win 98, MS office 97 pro. Sysinfo shows running at 80 to 82 %. Several probs: 1) attempt to replace borders on cells that have been moved or pasted does not work. the function in the tool bar is ignored by the system. the border function is not highlighted in the pull down menu.

2) making changes to sheets or creating new sheets cause the system to generate an error message and close down the program.

EXCEL caused an invalid page fault in
module EXCEL.EXE at 015f:3009aa90.
Registers:
EAX=00000000 CS=015f EIP=3009aa90 EFLGS=00010246
EBX=00000000 SS=0167 ESP=0062c638 EBP=0062c848
ECX=00000adc DS=0167 ESI=0062cd9c FS=122f
EDX=0000d000 ES=0167 EDI=00000000 GS=0000
Bytes at CS:EIP:
0f b7 58 02 83 7d 0c 00 0f 85 92 de 0e 00 c7 45
Stack dump:
00000000 0062cd9c 00000000 00000000 00000036 0062c680 815b4680 00407854 c1570770 0062c680 bff7a0fe bff7b317 00400000 00000000 0062ca4c 00407858

I have uninstalled and reinstalled Excel.

Any help is greatly appreciated

[email protected]
 

A:Excel is crashing system and its functions fail

Read other 11 answers
RELEVANCY SCORE 51.6

Hello,

I have a question about whether or not I can perform a specific task with MS Excel. I have a catalog including 3 columns of information: product code, product description, and unit price. I am currently trying to compile written orders that I have received, and I need to generate receipts to the customers. I would like to format the receipts the same way, including product code, description, price. However copy-pasting everything item-by-item would take very long. I would like to know if Excel has a formula wherein I can simply type in a product code, and then Excel will automatically recall the associated product description and unit price (which would be within the same row as the product code). Is this possible to do? I am not looking for a "find" function, I am trying to figure out if I can have Excel locate and then paste the corresponding product description and unit price into their own columns, if I enter a product code. Then I am hoping to just put in a sum formula for the grand total, etc. I don't know much about Excel, so I would appreciate any suggestions! I have tried some of the functions in the "Lookup and reference" subcategory but I have not figured them out. If you have any recommendations for programs besides Excel, I would be open to those as well. Thank you so much for your assistance!
 

A:MS Excel formula for reference/lookup functions

Read other 6 answers
RELEVANCY SCORE 51.6

Hello

I have a spreadsheet with lets say the following values

C1-9000
C2-1000
C3-{BLANK}
C4-{BLANK}
C5-{BLANK}
C6-{BLANK}
C7-{BLANK}
C8-1100

My cell uses the following formula to work out outstanding values.
=IF(OR(C8<=0,C7<=0),"",(C8-C7))

But now C7 is blank so my function will just be blank.

I need a function that if C8 has a value it must get that value and subtract the next value, whether it is C7 or C2. In this case, C2.

Please could someone give me a function to dynamcally decide what value to use.

Thanks
 

A:Excel Functions - skip cells if they are blank

i think this will work
But the numbers need to start at row 2
and needs to be entered into cell C2 and then as an array- so control+shift+enter - so that {} are around
=IF(C2="","",IF(OR(C2<=0,C1<=0),C2-INDIRECT("C"&(MAX(($C$1:C1<>"")*(ROW($C$1:C1))))),(C2-C1)))

need to work out how to start

basically
(MAX(($C$1:C1<>"")*(ROW($C$1:C1))))
will find the ROW number that is not blank
http://www.cpearson.com/excel/LastValueInRowOrColumn.aspx
we than add the Column C to the the ROW Number and use Indirect to use that in a formula
INDIRECT("C"&

and so we now have the value in the previous non blank cell
but to stop it using the same row - i have changed the range to look on in previous rows - hence the C1 starting point
 

Read other 1 answers
RELEVANCY SCORE 51.6

How do you trigger a custom function or sub in Excel?

For instance, in Access or VB, I can create a button that upon clicking will execute the sub/function. How do I do this in Excel?

MBN
 

A:Trigger Custom Subs and Functions in Excel

Read other 6 answers
RELEVANCY SCORE 51.2

Okay I have a odd new problem going on that just started last night (12/1611) where some hours later after I played around on shoutcast.com, and I dont know if that has anything to do with it, but what happens is:

My flash has a odd glitch that doesn't let any internet radio station play and flash cartoons play off slow and without sound either.

I cant even get any sound from my speakers period. They're usb speakers by the way.

Weird thing is rebooting my computer fixes it. Except it seems to come back again a few hours later. I did try running my anti virus program but it didn't find anything.

I also haven't downloaded anything new recently either.

OS: Windows XP 32 bit home edition

Edit: So far the problems only come up twice, last night and this morning except it hasnt occurred again. I'm really getting to wonder what's going on and if possibly it was some update that started it.
 

A:Solved: Time Bomb

I'm getting to think it was only a couple times only deal since it's been so long since it came up again so I'm plainly going to mark this thread as resolved. If it comes up again I'll jus reopen it again and ask for help than.

edit: Okay it happened again. I just tried uninstalling and reinstalling my flash player but if anybody else has any suggestions I'm for it. I mean since it also effects my speakers some how it really has me wondering.
 

Read other 2 answers
RELEVANCY SCORE 51.2

Is it possible to use functions that I define in the VB module in spreadsheet cells?
 

A:Excel: Possible to use user defined (VBA) functions in cell formulas?

Hi there,

Absolutely. A great addition with the VB5 library. Although, you could actually do it, very carefully, with the old Excel4Macros as well (but those are finicky and troublesome IMHO). Also, in Excel 2007, even though it defies everything ever said about UDF's, you can sometimes change a set of cells formatting via UDF as well! I wouldn't recommend it though, as I don't think it was intended to be able to do so, not really sure there. There are some things you should do to a UDF to make it 'spreadsheet cells friendly' though. Such as naming your variables, think of using Application.Caller method, not using Activesheet or Activeworkbook and such. Compare those to using the INDIRECT() function without leaving a reference (second syntax) on multiple sheets/books. If you have a UDF, feel free to post it up. If you don't have one and would like one, please post your requirements along with some sample data and the expected results.

HTH
 

Read other 2 answers
RELEVANCY SCORE 51.2

EXCEL has several functions to support complex number math: IMARGUMENT, IMABS, IMPRODUCT, etc.

The results typically have about 15 decimal places and may or may not be in scientific notation.

Is there a way to format the numbers in the results? To specify the number of decimal places? Scientific notation or not?

Thanks.
 

Read other answers
RELEVANCY SCORE 51.2

Is there any way to make functions in a word table that will automatically and instantly update other cells as you work the same as excel does.

We are creating a user form and want the information entered to update other cells automatically without the user having to click on the cell in question and select "update".

Thanks,
John
 

A:make automatically updating functions in word the same as excel does

Read other 9 answers