Over 1 million tech questions and answers.

# excel functions

Q: excel functions

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!!!

RELEVANCY SCORE 200
Preferred Solution: excel functions

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. A: excel functions

RELEVANCY SCORE 53.6

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

RELEVANCY SCORE 53.6

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

RELEVANCY SCORE 53.6

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?

RELEVANCY SCORE 53.6

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

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

RELEVANCY SCORE 53.6

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

RELEVANCY SCORE 53.6

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

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.

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.

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

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

A:Calculating VBA functions twice in Excel

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)

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

RELEVANCY SCORE 52.8

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

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

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

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

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

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.

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

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

RELEVANCY SCORE 52.4

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.

RELEVANCY SCORE 52.4

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

RELEVANCY SCORE 52.4

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

0004 Workstations
0004 Workstations
Sheet3
V W

0001
0001
0001
0004
0005

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

RELEVANCY SCORE 52.4

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

RELEVANCY SCORE 52.4

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.

RELEVANCY SCORE 52.4

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
End If
End If
Range("D8").Activate
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?

RELEVANCY SCORE 52.4

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")

RELEVANCY SCORE 52.4

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

RELEVANCY SCORE 52.4

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)

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
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

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

RELEVANCY SCORE 51.6

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.
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

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

RELEVANCY SCORE 51.6

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?

RELEVANCY SCORE 51.6

I have a contract start date and end date... dates are all ranges from 2019 to 2021 and longer

I have 4 columns for:
2018
2019
2020
Balance

I am trying to get a count of the months that contract are/were active based on 2 dates (start and finish). Seemed simple.... but not so much!

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

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

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.

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

RELEVANCY SCORE 51.2

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

A:Solved: Excel Functions Attn: Firefytr &amp; Bomb

RELEVANCY SCORE 51.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).

A:Solved: Converting excel functions from English to French

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

RELEVANCY SCORE 46

I have Office 2007 on Windows 7.

When I am working on a sheet (or document) the ribbon can become inaccessible, nothing works when I click it (like a 'Not Responding' screen).

If, I switch to another open sheet/doc and then switch back the ribbon is accessible again!

I have uninstalled/reinstalled Office plus SP's twice but the problem persists, and, as is usually the case, this does not happen all the time but enough times for it to be really annoying!

Any ideas?

Thanks for your time on this

Neil

A:Cannot access the Ribbon functions in Excel 2007 or Word 2007

Could you please post a screenshot or two?
And what are file sizes (MB)?

RELEVANCY SCORE 45.2

Hello,

I noticed that my cut and paste functions were not working normally in excel. When I would press ctrl+x the ants would not surround the selection, and when I would press ctrl+v the data would paste, but the old data would not be cut. Additionally I was not able to use the ctrl+alt+ v (paste special) function, which I use a lot!

After reading up on the issue I closed all of my google chrome windows and tried working in excel again. The cut and paste functions returned to normal, but the paste special option never came back. It is also grayed out on my toolbar menu.

I'm running Microsoft excel 2007, and I'm using Windows Vista (I know, I need to upgrade!)...

RELEVANCY SCORE 33.2

I need help with the following:

If cell A1 is < L1, then the answer is cell A1, but

If cell A1 is > K1 but < K2, then the answer is 100% of L1 plus 90% of (cell A1-L1), but

If cell A1 is > K2 but < K3, then the answer is 100% of L1 plus 90% of L2 plus 84% of (Cell A1-L1-L2), but

If cell A1 is > K3, then the answer is 100% of L1 plus 90% of L2 plus 84% of L3 plus 79% of (Cell A1-L1-L2-L3)
Cell A1 is the variable

K1=2400 L1=2400
K2=4000 L2=1600
k3=7143 L3=3143

How do I do this? I'm supposing I use nested IF functions, but I cannot get it to work. I need this late tonight if at ALL possible. Thanks so much!

Lisa

A:IF functions

This is a perfect candidate for nested queries, if a confusing one. To get it right, I recommend you use an outline sort of way to lay out your logic, then it comes together much easier. I recommend you actually start your logic design by setting it up this way:
logic: a1 < l1
true: a1
false: logic: a1>k1
true: logic: a1<k2
true: (l1+(.9*(a1-l1))
false: logic: a1>k3
true: (l1+(.9*l2)+(.84*(a1-l1-l2))
false: (l1+(.9*l2)+(.84*l3)+(.79(a1-l1-l2-l3))

Important notes:
(1) for the case you provide, k1 and l1 are the same and the others have known relationships. If you don't need a generalized tool, you could simplify the calculation, and
(2) you don't account for equal to conditions in your current logic -- so if I had something that was exactly 2400, what would you do with it?

This shows that you have accounted for all three states that you need for all the if statements...(except that you have to figure out where to make things >= or <= or you will have skipped several potential cases. (A good testing approach is to test for specific known values like 2400, 4000, 1600, etc. and make sure they are classified correctly; also test for negative values and very large values, just in case.)

hope this helps.
lika2know