Functions and Formulas

Help making an excel calculator.

Beckmanazo — Wed, 08 Sep 2010 20:03:08 GMT

I am a fairly new user of Excel and have been dabbling in it and teaching myself for some time now, but I am having a hard time finding any help on this particular subject. I don't know that what I am attempting to do is even possible in excel, but it is worth a shot here to see if anyone can help.

I am attempting to make a calculator that will take two number, the first number is the amount of inquires received and the second number is the goal of how many will need to be converted into sales. There are 4 different categories with different percentages that need to be met to hit the specific goal, so what I am attempting to do is have a calculator that will allow me to put in the # of inquires to be received, the specific goal for the salesman and then have it calculate what percentages they would need to operate at to meet their goal... I hope I explained it ok... Here is an example.

Inquires = 180

Inquiries to appointments = ?% 60-100%

Appointments to interviews= ?% 67-100%

Interviews to Commitments= ?% 50-80%

Commitments to sales= ?% 67-100%

Goal=25

Now for each area that needs a percentage filled in by Excel there is a range that is acceptable production and depending on what the goal is they will increase or decrees with the goals they expect to hit, which are provided above. The way it is set up is that each following percentage is then fed from the results from above... another example of how it would read when done...( I am using whole number to cut down on confusion)

Inquires = 180

Inquiries to appointments = 60%=108

Appointments to interviews= 67%=72

Interviews to Commitments= 50%=36

Commitments to sales= 67%=25

Goal=25

I hope this is explained well enough, I am having a hard time wrapping my mind around how it should work... or even if it can work. If you can help I would much appreciate it!

if statement with ranges

TGM — Mon, 23 Aug 2010 15:32:16 GMT

This is my question/issue – I am trying to create an estimating tool for software projects. Based on 3 or more project types (A, B, C) the three major roles (business analyst, developer and quality assurance) have different level of efforts (time they will spend on the project).

I created the following formula to calculate/estimate each effort based on project type (A, B, C) estimates =IF($D7="A",'Project Types'!$B$4*$T7,IF($D7="B",'Project Types'!$B$9*$T7,IF($D7="C",'Project Types'!$B$14*$T7,"TBD"))), for this example let say this formula returns “20” in the cell.

What I would like to do is calculate a range, say plus or minus 30%, thereby giving me “14 – 26” in the cell.

I know I could probably add various cells and do a bunch of calculations and then concatenate the results – but what I would like to do is handle this through 1 formula. Any suggestions?

Making hyperlinking complicated

Omar — Tue, 24 Mar 2009 03:46:48 GMT

I have a habit of driving to another city and back to get next door sometimes. That's how I feel with what I just did to make hyperlinking within one spreadsheet work.

After typing everything below, I decided I'd never be able to explain all this, so have attached a file that shows all the navigation I'm doing. It's getting late, and I'm having trouble determining if I've been unclear anywhere, or have left out key information. Sorry if I have. Even though I've made the whole mess work, I can't help but feel there is a better way to do this that is easier to do the next time. Any ideas?

It seems really odd that I can't just use something simple like:

=hyperlink(rangename,"Go there now")

Instead, I used the following just to jump from one tab to another, depending on the contents of a cell:

=HYPERLINK(HyperFileName&b2&"!basecode","Go to Cost Sheet for: "&b2)

HyperFileName is a range name pointing to a cell that contains the following formula:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)

The result this formula gives is: [filename.xls]

These gymnastics are necessary to meet a couple qualifications I have for this project. None of my formulas should be dependent on the file location, and none should be dependent on the file name. Also, the specific tab that the hyperlink refers to will change according to the contents of cell B2. Any of the target tabs will have a range name of "basecode".

A second type of hyperlink I built allows me to position the cursor on a specific row, depending on what text was in the cell. I've broken it into three lines hoping that will make it readable.

=HYPERLINK(HyperFileName&"pricelist!"&CELL("address",INDIRECT("a"&

MATCH(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),

PLModelList,FALSE)+1)),"Go to Price List")

Anyway, after all that, I'd be happy to hear some ideas for navigating around a spreadsheet using hyperlinks.

Remove all Numbers from a Column

autigers71 — Fri, 13 Aug 2010 11:48:23 GMT

I am working in multiple sheets that I can copy the data in the sheet and then seperate into cells with find and replace but can't do with the #'s in a range. I am wanting to remove the numers that are in the cells that range from 1-600 without removing the alphas.

26 Tom Seaver

58 Bob Barker

158 Clive Taylor

And so on....This are each in a seperate cell in one column and I need to just remove the #'s. I have 600 lines to go through and right now I click in the cell and delete the # by hand. Any help is appreciated.

Excel 2007 - multiplying across columns even if column is empty

audrey — Tue, 10 Aug 2010 18:38:16 GMT

In this instance, mutliplying a x c is a constant to arrive at the answer for d; however b may be empty. how do I write the formula? If I enter =(+a1*c1)*b1 - if b is empty it will not perform the multiplication.

a	b	c	d
Estimated Quantity	Occurance	Rate	Amount

1		$25.00

Thanks for the help!!

Another time calculation.

d56auction — Tue, 10 Aug 2010 15:02:03 GMT

I am paid $5 more for hours worked between midnight and 7am. I already have a time calculation so it is imperative I use the 24 hour, hh:mm format. I think I need an IF statement that says if the clock out hour is >00:00 and < 07:00 then I want to return a value in the chosen cell = the difference. The returned value needs to be in standard decimal format.

e.g.

clock in 16:00

clock out 01:15

"bonus" pay eligible hours 1.25

Any ideas? Hope this is clear. Thanks in advance.

Time formula

d56auction — Fri, 06 Aug 2010 18:34:53 GMT

Hello,

Seems to be a simple request but it has me stumped. A simple time sheet... Column A is time clocked in (24 hr clock) Column B is clock out. Column C is the difference expressed in hours in a decimal system.

So, for example I'd like clocking in at 1600 and clocking out at 0015 to give me 8.25 hours. I have both the two columns set as 24 hour time but when I do a simple subtract function in column C it goes haywire.

Any ideas...

Thanks,

Jack

Matching criteria in 2 columns to look at a 3rd column of numbers to create a sum

jimbo5129 — Mon, 09 Aug 2010 22:19:07 GMT

Here is a typical row in the spreadsheet which is nearly 4000 rows of data:

Description WBS WBD Material Qty Mea Size

| Ball Valve, V600, 02 | C12DO | F1804B | CARBON STEEL, PIPING | 12 | EA | 1" |

Im trying to use the "Description" & "WBS" columns for the criteria to match. There are multiple lines that

are identical but the QTY column will be different. I want to add those up from the Qty column when the criteria

is met.

Thank you for any help on this...driving me Crazy and thats a short drive for me!

Jimbo

Dealing With Negatives

ian green — Sat, 07 Aug 2010 14:04:00 GMT

I have a sheet containg two columns, frmatted as currency and to show negative numbers ehere approriate. e.g -£25.00

One colum shows a positive number of £3.00 (Cell A1) whilst the other shows -£1.00. (Cell D2)

When I do the =SUM formula as =SUM(A1-D2) I get the result of £4.00.

Can anyone tell me what I am doing wrong please?

Thank you,

Ian

Not sure if Possible

autigers71 — Sat, 07 Aug 2010 19:15:36 GMT

I am trying to have a row with 1.1 - 32.9 but I need to skip all .0. So it should go 1.1-1.9 then 2.1-2.9 and so on. Is this possible without having to changege each .0 to .1 manually? I have to do this on 40 different sheet and I want to save some time. Thanks

Value Comparison Formula

taino8632 — Tue, 03 Aug 2010 16:41:24 GMT

Excel 2003. We are capturing grades for site reviews based on the scale listed below. On the report we need to indicate how the current grade compares to the grade from the previous period.

Score	Grade
1	F
60	D-
64	D
67	D+
70	C-
74	C
77	C+
80	B-
84	B
87	B+
90	A-
94	A
97	A+

If the grade improves or declines within the same letter grade, it is marked as slightly up or slightly down. For example, B - to B would be considered slightly up.

If the grade improves or declines outside the same letter grade, it is marked as up or down. For example, C- to B would be considered up.

The grades are entered using actual values. For example, site one has a grade of 93.5, site 2 81.5 and so on. I use a lookup to convert the score to a letter grade based on the table above.

The comparison can be done using the scores (Week 1, Site 1 93.5; Week 2 Site 1 89, etc). However, I have been unable to find an efficient method to do this.

We are currently using this formula.

=IF(AND(C3="A",OR(G3>="B",G3="C",G3="D",G3="B-")),IF(G3="A-","Down","Up"),IF(AND(C3="B",OR(G3>="C",G3="D-",G3="D+")),IF(G3="F","Down","Up"),IF(AND(C3="C",OR(G3>="D",G3="D-",G3="D+")),IF(G3="F","Down","Up"),IF(AND(C3="B",OR(G3<"B")),IF(G3="F","Up","Down"),IF(AND(C3="C",OR(G3<"C")),IF(G3="F","Up","Down"),IF(AND(C3="D",OR(G3<"D")),IF(G3="F","Up","Down"),IF(C3=G3,"No Change","")))))))

However, it is not very efficient and does not encompass all possible permutations. What other method could be used?

Any assistance is greatly appreciated.

Need help to get Formula for copying characters

nina_deanna — Mon, 02 Aug 2010 04:18:31 GMT

hi everyone..

I reall need help to come out with formula how to get the result as in Column B where it copy anyname inserted in column A as below and 10 rows to be filled up for each name. Its easy to just drag down over the row to get the result, but this need to be done for the name list over 2000, really need a formula on this.

thanks,

Diana

A B

Name	Person incharge
Jason	Jason
	Jason
	Jason
	Jason
	Jason
	Jason
	Jason
	Jason
	Jason
	Jason
Diana	Diana
	Diana
	Diana
	Diana
	Diana
	Diana
	Diana
	Diana
	Diana
	Diana
Edward	Edward
	Edward
	Edward
	Edward
	Edward
	Edward
	Edward
	Edward
	Edward
	Edward

Nested IF statement Driving Me Crazy! Please Help

Mubeen — Fri, 30 Jul 2010 13:42:33 GMT

Please any1 evaluate and let me know where i am going wrong. All cells produce an OUtcome of J regardless of the amounts being evaluated.

Using Excel 2007

=IF(F18<3579,"A",IF(F18<4349,"B",IF(F18<5299,"C",IF(F18<6089,"D",IF(F18<6969,"E",IF(F18<8829,"F",IF(F18<10549,"G",IF(F18<12119,"H",IF(F18<17549,"I",IF(F17>17550,"J",0))))))))))

Need Formula to Show Percentage Met

toriacat — Fri, 30 Jul 2010 20:45:29 GMT

Hello,

I have a column (H) that has either "met" or "missed" in the rows based on an IF formula. The IF formula is comparing the desired date to the completed date: =IF(D2=C2,"Met","Missed")

I now need to do a percentage of the met. I don't think I can do that on column H since that is all text, but I really don't know how to do this.

Thank you!

Need help pulling info from multiple sheets to one sheet based on matching criteria

mrtoyz — Wed, 14 Jul 2010 22:14:31 GMT

I have a spreadsheet comprised of multiple pricing sheets. I am trying to make a price tag on its own sheet. I would like to enter a model number on the price tag and have model detail pull from the pricing sheets and fill in my price tag. I'm really not sure where to begin. I'm a little worried that i have merged cell on the pricing sheets and that may be an issue...

I'd really appreciate an guidance.

-T

Looking for help with IF nested function limit

mrtoyz — Mon, 12 Jul 2010 17:22:59 GMT

I've researched a few options but havent been able to make anything work.
Here is the formula I know would would if the limit were not 7....

=IF(C5="AT",K5*9.5%,IF(C5="Cr",K5*9.8%,IF(C5 ="Du",K5*9.8%,IF(C5="OH",K5*9.5%,IF(C5="PWC",K5 *9.5%,IF(C5="Sc",K5*9.8%,IF(C5="Sp",K5*9.8 %,IF(C5="To",K5*9.8%,IF(C5="Tr",K5*9.8%, IF(C5="UTV",K5*9.8%))))))))))

Well I've added a few more values now totaling 14...

I tried this and I get a "false" AND then the correct answer, obviously because the correct value is in the second IF statement.

=IF(C5="ATV S",J5*9.5%,IF(C5="ATV U",J5*9.5%,IF(C5="Cr",J5*9.8%,IF(C5="Cr T",J5*9.8%,IF(C5="DS",J5*9.8%,IF(C5="MX",J5*9.5%,IF(C5="Off", J5*9.5%)))))))&IF(C5="PWC",J5*9.5%,IF(C5="Scr" ,J5*9.8%,IF(C5="Sta",J5*9.8%,IF(C5="Sp",J5 *9.8%,IF(C5="Sp T",J5*9.8%,IF(C5="Tr",J5*9.5%,IF(C5="UTV",J5*9.5%)))))))

The I tried this, but I get "formula contains an error"

=LOOKUP(C5,{"ATV S","ATV U","Cruiser","Cr T","DS","MX","Off","PWC","Scr","St"," Sp","Sp T","Tr","UTV"},{J5*9.5%,J5*9.5%,J5*9.8% ,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%,J5*9.5%,J5*9.8%,J 5*9.8%,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%})

I'd REALLY appreciate any help you can give...I'm stumped.

-

Retain date and remove text from cell

renjithplr — Tue, 13 Jul 2010 06:15:06 GMT

Hello,

Is there any formula, to delete text and retain only dates.

Let me explain more detail.

I have a excel file with more than 1500 entries.

One of the column has date and text separated with space, but now my boss told me to remove the text and retain only date.

Date format is MM/DD/YYYY.

Eg. 6/10/2009
(see Signature Block, Optical Transport Service/Longhaul Service Order Form)

i want to retain 6/10/2009 and want to delete the all other text.

It would be easy if there is any formula to do it.

How to Define #REF and #VALUE errors

Slipstream2904 — Fri, 09 Jul 2010 18:12:59 GMT

I need to create a formula that can differentiate between these 2 errors. I have tried a lot of different methods so far, but none of them will differentiate between the different types of errors.

I do not know how to use the VB code in excel or even where it is, so I need to find out if I can do this without using it.

I have a series of combobox dropdowns that rely on input from previous selections, some rely on multiple previous variables. If the error is #REF it means that the index has been put out of range and should display "-----" (since the "ignore blank" doesnt seem to work when you return an error or have a function returning a blank in a cell), and if it is "#VALUE it means the selections made have built a combination of objects that are larger than the allotted volume and needs to display "Volume too Large".

Combo1 Index Combo2 Index

A1 B1

A2 B2

A3 B3

A4 B4

If A1 is selected, B1, B2 & B3 will show up with B4 being "-----" (obviously stating not to choose it).

If B1 is selected, B1 will display "Volume Too Large", B2 will show up, and B3 anc B4 will display as "-----"

Thanks for any help.

how to get count of comment inside cell

renjithplr — Tue, 29 Jun 2010 06:35:18 GMT

Hi,

I have a question, how to get the count of comment inside a cell (insert comment).

I have attached a file which has comment. please guide me.

Formula needed

Wendy — Thu, 17 Jun 2010 04:44:52 GMT

Hi all

Im doing a workbook that has a total of 7 sheets. What i want to do is on the first sheet column A has a list of names, which has over 200 rows.Column B has the financial year ends. I have created a form with a text box and have the button on the first sheet. When you click on it, the text box will open and then you type in the financial year end and click ok. All i want it to do is any of the names in column A of that financial year end to copy to the 7 sheets. Their can be upto 15 names. But for the life of me i can't seem to work out the code i need.

Boy i hope i make sense.

How to work on Vlookup with Dates as criteria?

geng — Thu, 17 Jun 2010 05:14:36 GMT

I created a simplier and easier sample so not to confuse myself and the one who's helping me.

I just need to return a certain data to the cells higlighted in yellow, based on the criteria on B6 and C6.

The detailed explanation is on the attached workbook.

I really appreciate your help.

Thanks!

highlight past date

Wendy — Fri, 11 Jun 2010 01:45:44 GMT

Hi all

What i want to do is have a column of dates that are a few months away. But when we get past the dates entered i want those dates to automatically change to red to let me know that they are past due. Hope this makes sense.

Wendy:)

Need formula to calculate average number of days

br7250 — Wed, 09 Jun 2010 19:59:52 GMT

The worksheet is defined as follows: In cell A2 is the date 5-10-2010, in cell A3 is the date 5-14-2010, in cell A4 is the date 8-31-2010 in cell A5 is the date 10-17-2010. In cell B2 is the value 1, in cell B3 is the value 2, in cell B4 is the value 3, in cell B5 is the value 4, and in cell B6 is the value 5. In cells C2 through C6 a value of 1 is entered in each cell. In cell A1 is the word Date, in cell B1 is the word Days, and in Cell C1 is the word Count. In cell A9 is the date 5-10-2010, in cell A10 is the date 5-1-2010, and in cell A11 is the date 10-1-2009. The date format is entered as month-day-year. Here's what I need:

In cell B9 I need a formula that will calculate the average number of days from column B from the beginning date entered in A9 plus 6 days (a week).

In cell B10 I need a formula that will calculate the average number of days from column B from the beginning date entered in A10 plus 30 days (the month of may for the year 2010).

In cell B11 I need a formula that will calculate the average number of days from column B from the beginning date entered in A11 plus 364 days (the fiscal year ending in Sep 30, 2010).

So, the result in cell B9 should calculate the average number of days based on 5-10-2010 and 5-14-2010, as these are the only two dates encompassed in the week time frame for cell B9.

For B10, it would include only 5-10-2010 and 5-14-2010. For B11 it would include all of the dates with the exception of the last date (10-17-2010).

Thank you,

Bruce

How do I create a fomula that adds a percentage dependant on score???

Nikkiq — Wed, 09 Jun 2010 15:14:36 GMT

Hi All,

I am new to this site and although I use excel every day I am struggling to write a formula!

I have a list of staff salaries and each person will be awarded a score of between 1-3 by their line manager, I then need to be able to add a percentage wage increase dependant on that score.

For example if they score 1 they will get 0.5% increase, but if they score 3 they get 1.5% increase.

They spreadsheet needs to be all tidy and easy for non-finance managers to look at so all they have to do is add the score and see what happens!

Sorry if this is really basic, but its confused me!

Many thanks

MATCHING TEXT OF TWO COLUMNS AND WRITING VALUE FROM THIRD COLUMN

MPowell — Tue, 18 May 2010 20:06:54 GMT

I am hoping someone can help.

I need the formula to do the following:

Column A Column B Column Ctext text number (acct ID)

I want to match the text in ENTIRE Colimn B to ENTIRE Colimn A (named account name), when there is a match by account name, write the ACCOUNT ID FROM COULMN C

tHANKS!