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

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.

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

Read other 7 answers


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


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


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

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



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

Hi. can someone help me simplify this formula?


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.

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

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

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


Read other 3 answers

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

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

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

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



Read other 3 answers

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

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


Read other 2 answers

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


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


A:Solved: Excel functions

Read other 6 answers

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.


A:Excel Help: Functions and formulas

Read other 13 answers

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

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.


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:


Read other 2 answers

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

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

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 -


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.


A:Nested if functions in excel

Read other 6 answers


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

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.

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?


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

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.


A:Solved: Need help with complex excel functions

Read other 16 answers

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


0001 Computadoras de Escritorio
0001 Computadoras de Escritorio
0004 Workstations
0004 Workstations
0005 Servidores para computadora


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

thank you in advance

Read other answers

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

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?


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

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:

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

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

Then under the ThisWorkbook object I have...

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

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:

A:Excel nested Round with OR functions

Here you go, should work (posting from phone) ...

Read other 3 answers

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

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

A:Solved: Excel sum and average functions

Read other 16 answers

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

should give you that info

so in A12 type
=mode( a1:g10)

Read other 3 answers

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


I have a spreadsheet with lets say the following values


My cell uses the following formula to work out outstanding values.

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.


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

need to work out how to start

will find the ROW number that is not blank
we than add the Column C to the the ROW Number and use Indirect to use that in a formula

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


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


A:Solved: Calling excel functions from access

Read other 6 answers

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.


Read other 1 answers

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:
That is nested in an index formula, INDEX(array,row_num,column_num), to result in:

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

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

I have 4 columns for:

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!

Read other answers

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?


A:Trigger Custom Subs and Functions in Excel

Read other 6 answers


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

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?


Read other answers

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.


Read other 2 answers

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

Read other 12 answers


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

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


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

Read other 9 answers

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


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

Read other 1 answers


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

Read other answers

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!


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.

Read other 3 answers

Hello, I bought an HP 255 G5 without OS. I installed Win 7 and all the drivers but the functions activated by the key fn don't work. For exampe, if I want change the lightness of the screen, the combination fn + f2 doesn't work. How can I activate the fn functions? Thank you very much.

Read other answers