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

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

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.

I am trying to automate a Summary Report by creating a formula that Counts the number of Items across (3) categories.

The complication I cannot get past is that one of the three categories has a Non-Standard range of values. I created a lookup table to try to make the formula easier - still cannot figure out a Formula to work(may require a function?).

I am trying to aviod a macro that physically seperates the source data into (4) seperate spreadsheets, which then can then have hard-coded formulas based on the four spreadsheets for each Company (CoA, CoB, both CoA & CoB, Neither CoA or CoB).

I wish Excel 2003 supported CountIFS(), but even with Excel 2007, I still cannot figure out how to include the complex Range. Ultimately I want a solution that is compatible with Excel 2003.

Source Data: Sheet 1

Lookup Table(for criteria with Non-Standard range of values): Sheet 2

Summary Report: Sheet 3

I'm creating a project plan in excel for my team. I have a column of tasks aligned with a column of team member names. I'd to create a table that searches the "name" column and then returns a listing of each unique name with all tasks assigned to that name. For example: if I have 3 team members: Mike, Suzie, Paul, but there are many tasks assigned to each of them, I'd like to return a table that lists all of the tasks for Mike, Suzie, and Paul in 3 columns. Is this a pivot table? What is the best way to create this?

Hello Folks and thanks in advance for taking a look at this. I have taken on the responsibility of managing a complex ranking system and I'd like to know if there is a way to use Excel to help me. Basically there is an ordered list of people and sometimes someone of the list will move to the bottom. The complex part is that some of the people on the list have locked positions. When everyone else on the list moves up, they do not. To complicate matters further, people change their moving/locked status on the list often. I began trying to use nested if statement to create two lists, one for people that were moving and one for the locked people, thinking I could then just recombine the two and sort them, but to no avail. Any ideas? Thanks again.

Hi,

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

thanks

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

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.

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

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

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.

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?

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

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

Fairly Experienced Excel user but terrible with Databases (this may require a Database)

I have a Main Array with an index (sorted single word names, first column) and 6 additional data fields which may or may not contain data.

Sample

threesiks 8 T44 M6A2E1

tomcat0870 5 5

Trexdog 10 T30 M26

tungstun 8 IS3 ISU-152 Ferdi

I have 4 additional single column arrays which are not sorted but contain data in a preferred order (top down preference).

Array 1

T30

IS7

Maus

T34

IS4

Ausf B

T30

IS3

Lowe

M6A3E1

KV-5

Array 2

TD-704

JT-JagdTiger

Ferdi

ISU-152

M26

So, I have the name of a person say Trexdog. I need the result to look like

Name Array1_results Array2_results

Trexdog T30 M26

The T30 is pulled from array 1 as the most preferred piece of data.

the M26 is pulled from Array 2 as the most preferred piece of data (since the values above it are not present)

Total output from the above would be

threesiks M6A2E1

tomcat0870

Trexdog T30 M26

tungstun IS3 Ferdi

Note that Tungstun has 2 separate results in array2 but only the more preferred piece of data is displayed.

I need to write a formula on excel that will do the following:

When someones income is between 2 fixed points they are liable for a charge in £500 increments. For example if they have £500 more in their savings than that which is allowed then they are charged £1. Each subsequent £500 is charged at £1 until the maximum is reached. The first trigger is £10000 and the last point is £1500. Any help would be greatly appreciated.

Hi guys,

I'm trying to sort a bunch of live data linked from another worksheet into a table. Basically, Column A contains a list of 'sites', and then under each site there are a number of documents. The number and name of documents varies (i.e. some sites may have 2, some may have 6).

What I want, is to have a table that lists all the sites in the first column, and then any documents to be put into other columns to the right (same row as site name). It is important that this is 'live', that is, if a document is added to a site, I want it to appear on the table.

I've attached a sample document as an example.

Thanks in advance.

I want to create a MS Query for an Excel pivot table to return net sales information from an SQL database. Included in the available data is cost of sales info but problem is cost info is positive for both invoices and credit notes and I need to return a negative cost for credit note lines. Credit notes can be differentiated as they have a negative value in the quantity sold field. I want to create a column in my query that returns the cost field value as a negative value.

If I was to write a formula in a spreadsheet it would read =IF(QtyCell<=0,-1*CostInfoCell,CostInfoCell) Can anyone provide me with direction on how to return this calculation in MS Query?

Cheers,

Iain

I need to do an IF formula w/ a time entered. Every time I attempt this it gives me an error pop up. I need to do a scoring of timed calls

Less then 3:30- score of 5

3:30-3:59- score of 4

4:00-4:30- score of 3

4:31-5:00- score of 2

and anything over 5min score of 1

Any idea how to successfully do this????

I need HELP !!!! I need a simple macro that will simply move the cursor around the spreadsheet ( right,left,up or down) without embedding the ending cell address in the macro itself !

EXAMPLE: Macro 1 - to move the cursor (up) 1 cell, to the (right) 2 cells and ( down) 1 cell. The key is, I need the macro to do this each and every time it is executed, in this pattern, no matter where it starts ( assuming it is not blocked by margin constraints )

PLEASE !!!!!!! I'm going crazy !

cheyenne

Hi guys,

So here's what I'm trying to do. Basically I need to create a word document report from data in an access database. The access database basically contains risk items for different entities. So, the report will contain all the risk items for one specific entity and a summary.

What I want to be able to do is enter all this information into the database and then automatically generate the report. The database is basically setup, including the queries I need. I just don't know how get the information over to word in the way need.

The main problem is that I need a summary section that lists the number and types of risks found above, and then the details of each individual risk below. So a very simplified example:

Report for ENTITY 1.

Summary

1 High Risk

2 Medium Risks

6 Low Risks

Detailed Information

Risk 1: RISK 1's NAME

Severity: High

Infomation: This is the information about risk 1.

Risk 2: RISK 2's NAME

Severity: Medium

Information This is the information about risk 2.

...

I know that this is not a standard "mail merge", but I thought it might be possible using VBA. I have used a (very) little VBA, but I am willing to learn more. I just need a push in the right direction as to where I might get the information I need. Several other things I would like to be able to do (but could sacrifice if necessary) are pulling images stored as OLE objects in the access db into the report for each risk and being able to control the backg... Read more

Two days ago, Windows Update deleted my printer and its drivers from my system. I was able to get the drivers from Canon and put everything back together, but was not happy about it. Then this morning I found that another update had caused Excel 2007 to choke and fail to load any of my big spreadsheets that had complex macros, especially those using VBA. I use these daily.

What I need right now is: Which update did this and how do I back it out????

Then I need someone at Microsoft to recognize that their new update business model is horrible. I really need to see which updates are coming and what they are going to do before I allow them to be installed on MY system. And the included in information I need to see with each new update is instructions on how to remove it in case it causes problems.

Right now I'm in trouble. I can't open about a dozen spreadsheets that I use daily. Some are personal, but I'm the Treasurer of my local Homeowners Association and all of our financial records are in Excel and are currently offline due to this problem. I need it fixed ASAP.

Hello pencat12,

If you like, the tutorial below can help show you how to uninstall a Windows Update.

Windows Update - Uninstall in Windows 10

Afterwards, the tutorial below can help show you how to hide the update to prevent it from being reinstalled.

Windows Updates - Hide or Show in Windows 10

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.

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

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

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?

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.

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.

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

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

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

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)

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.

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.

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.

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

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

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

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?

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.

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.

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.

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

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

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.

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

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

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

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.

=mode(range)

should give you that info

so in A12 type

=mode( a1:g10)

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)

Hi CaliMac10, and welcome to TSG.

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

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!

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

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

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