Over 1 million tech questions and answers.

Auto-filling Formulas - Excel 2013

Q: Auto-filling Formulas - Excel 2013

Hi everyone,
 
I'm having some trouble autofilling an Excel formula. On my 'Data Entry' tab, there are several columns of data. For every three data entries, there is a merged cell in the column adjacent to the data that performs a 'SUM' function. For example, cells C4, C5 and C6 are summed in a merged cell that covers D4, D5 & D6. 
 
I am trying to transpose the data in the merged cells into another tab for analysis and other functions, including plotting. The formula for this is quite simple - " ='Data Entry'!D4 ". This is in cell A4 of the 'Processing' tab. In Cell A5 of the processing tab, I want the data from the next merged cell on 'Data Entry', so this is cells D7, D8 & D9. Again, the formula is simple - " ='Data Entry'!D7 ". I have entered these two and have the result I need. Problem is, there are several thousand merged cells that need transposing into 'Processing'. When I try to autofill my formula down, it fails to skip the cells in between and starts doing this:
 
='Data Entry'!D4
='Data Entry'!D7      (So far, so good)
='Data Entry'!D6      (Returns a zero value, because it is merged with cells D4 and D6 on 'Data Entry')
='Data Entry'!D9
='Data Entry'!D8
 
And so on.... I've filled in several formulas to try and get it to work, but no matter how far I go (I got to D34) it just refuses to work.
 
I know that I can use the formula " ='Data Entry'!D4:D6 ", and that this will solve the issue, but this will not plot in the way I need it to. I need a 'Scatter with Lines' graph, and when I tried plotting the transposed data using this formula, the lines on the graph were notably absent. As far as I can tell, this is down to the formula, because it works fine when I use the " ='Data Entry'!D4 " style formula. 
 
Please let me know if any other information is required - I've tried to be as descriptive as possible without confusing the issue. 
 
Many thanks in advance
 
CBR
 

RELEVANCY SCORE 200
Preferred Solution: Auto-filling Formulas - Excel 2013

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: Auto-filling Formulas - Excel 2013

How are you doing the "autofill"? Are you doing it with highlighting the entered cells so far than then "pulling" the handle at the bottom right corner of the highlighted cells? Or are you copying and pasting? It may not matter, but if you have only tried one way, then try the other.That is my best thought, which might not be much.

Read other 7 answers
RELEVANCY SCORE 88.4

Hi,

I am having problems trying to fill formulas in Excel 2007 for the rest of the Summary worksheet. There are basically other 4 Qtr1 - 4 worksheets in the workbook with exactly the same layouts. Under the selected cell, I got the formula =SUM('Qtr1:Qtr4'!B5), after which i tried doing the normal dragging to try to fill up the other columns but it did not seem to work. After which, I went to Excel options > Proofing > Autocorrect options > Autoformat as you type > fill formulas in tables to create calculated columns, it is ticked. I do not know what is the problem? Can someone help me? Thanks. Would really appreciate a lot.
 

Read other answers
RELEVANCY SCORE 77.6

I'm having an issue with an excel formula.

I have:
Code:
=
IF(D7="DSOA",'Common Milages'!D8,
IF(D7="SRHS", 'Common Milages'!D6,
IF(D7="ECT", 'Common Milages'!D7,
IF(D7="-","0",
))))
This code works fine on a single cell. The problem occurs when I try to duplicate it, either by using 'fill series' or copy and paste.

Excel automatically increments the cell numbers, so that D7 is D8, and so on as it goes down. This is a good thing.

But the formula also references a second sheet 'Common Milages'. The cell values in the formula that references this sheet always needs to stay the same. In other words 'Common Milages'!D6 should always be 'Common Milages'!D6, no matter where the formula is located.

Is there a way to exclude certain cell numbers from auto-incrementing?
 

A:Excel Formulas: Cell Value Auto-Incrementing Issue

Read other 6 answers
RELEVANCY SCORE 58

Hello all,Hoping someone can point me in the right direction on this. I have a user on our network (win2k servers, XP pro clients) who logs into the domain but uses a local (non-roaming) profile as he travels with his machine off-network quite often. When he opens up IE and types in "www.a" into the url it finds / prefills with "www.anot-goodsite.com", not exactly what you want to come up in front of a client, especially when your corporate website starts with "www.A" I've cleared cache & such, ran all the "normal" spyware removal tools, have good virus protection, etc. and have been unable to find a fix for this. Interestingly enough, it only happens when logged in on that computer as that particular user, so it appears to me that it's part of his profile. I'm reluctant to delete his local profile as there is a LOT of setup done on this particular machine that would be time consuming to reproduce. Any help or ideas would be greatly appreciated.BobMod Edit - Link to inappropriate site altered - Leurgy

A:I.E. 6 auto-filling searchbar?

In IE, go to Tools>Internet Options>Content>Autocomplete>Clear forms. That is where you can clear the URL's history and disable saving URL's. You can also run Ccleaner to delete index.dat files to remove the history.

Read other 2 answers
RELEVANCY SCORE 58

so my chrome used to open up and have username and password already there all i needed to do was press login now i need to select my user name and password from a drop down menu why is this happening and how can i fix it

my chrome setting have auto fill on
 

Read other answers
RELEVANCY SCORE 57.6

Hi guys, im trying to find out how to solve this for really long time, so the last option is to ask someone more experienced - you! :)

My problem:
After filling a webpage with informations and choosing from dropwdown menues and selecting files to upload, I want to save this, to send this "filled' page multiple times in time. All the files to be uploaded will be in the future in the same place on the computer, so i hope its not a problem.

What im looking for:
Some software, add-on, browser plugin, anything, that will just by clicking fulfill the information once again like i did last time. I have more then 1 version of that what i want to save like this, so just saving it like Project 1, Project 2 etc would be enough.

Example:
For once i have filled the whole page with alot of info, choosing from drop down menues and chosen few photos that will be uploaded with sending the page. I will hit the "button" that will save all this filled info and i will name it Project 1.

After 1 week, i have to fulfill that same page again, so i just hit the Project 1 "button" and all the info will appear. Something like more complicated copy paste :)

Hope I well expressed myself. If you dont understand some part, let me know. I will try to explain it in more details :)

A:Auto-filling form pages

Hi, welcome to TSF

RoboForm is one of the most popular programs of this type. It should be able to do what you're asking. See here for details: How RoboForm Works (scroll down to the 'Fill Forms Fast' section)

Depending on which browser you're using, there are several other alternatives, like this Firefox extension: FillForms

Read other 2 answers
RELEVANCY SCORE 57.6

Hi guys, im trying to find out how to solve this for really long time, so the last option is to ask someone more experienced - you! :)

My problem:
After filling a webpage with informations and choosing from dropwdown menues and selecting files to upload, I want to save this, to send this "filled' page multiple times in time. All the files to be uploaded will be in the future in the same place on the computer, so i hope its not a problem.

What im looking for:
Some software, add-on, browser plugin, anything, that will just by clicking fulfill the information once again like i did last time. I have more then 1 version of that what i want to save like this, so just saving it like Project 1, Project 2 etc would be enough.

Example:
For once i have filled the whole page with alot of info, choosing from drop down menues and chosen few photos that will be uploaded with sending the page. I will hit the "button" that will save all this filled info and i will name it Project 1.

After 1 week, i have to fulfill that same page again, so i just hit the Project 1 "button" and all the info will appear. Something like more complicated copy paste :)

Hope I well expressed myself. If you dont understand some part, let me know. I will try to explain it in more details :)

A:Auto-filling form pages

Hi, welcome to TSF

RoboForm is one of the most popular programs of this type. It should be able to do what you're asking. See here for details: How RoboForm Works (scroll down to the 'Fill Forms Fast' section)

Depending on which browser you're using, there are several other alternatives, like this Firefox extension: FillForms

Read other 2 answers
RELEVANCY SCORE 57.6

On the computer running xp every search box fills with zeros and will not let me delete them or enter anything else. Is this a virus and how do I get rid of it?

A:Auto filling search boxes

Perhaps a stuck key on the keyboard? Have you tried a different keyboard?

Read other 3 answers
RELEVANCY SCORE 57.6

I use my xp computer to download and recently whenever I go to any search box this fills completely with zero's and when I try to delete they fill again.
It not a sticky key as I use the same keyboard for my vista machine.
 

A:auto filling of search boxes

Hello,

Have you scanned your computer for malware?
 

Read other 3 answers
RELEVANCY SCORE 56.8

Google Toolbar not auto filling in IE12. Don't know what is wrong as I have it set to auto fill!

A:Google Toolbar not auto filling in IE11

There seems to be a problem, quite a few are having issues,

IE11 Google Toolbar no auto search suggestions, no search history? - Windows 10 Forums

You sure about that version of IE? I thought we were still on 11.

Read other 4 answers
RELEVANCY SCORE 56.8

Google Toolbar not auto filling in IE12. Don't know what is wrong as I have it set to auto fill!

A:Google Toolbar not auto filling in IE12

There seems to be a problem, quite a few are having issues,

IE11 Google Toolbar no auto search suggestions, no search history? - Windows 10 Forums

You sure about that version of IE? I thought we were still on 11.

Read other 0 answers
RELEVANCY SCORE 56.8

I have been asked to help with Pledge management at church and having manipulated lots of numbers into order I am now trying to do two things (1) is to show whether a pledge is equal to or more or less than the previous year; seemed simple enough to compare two columns and then a assign an answer, however I can do any one of the three comparisons and get the correct answer but fail to string them together and get the correct answer....I have been trying to use the if/or function. (2) if I could then sum how many of each the Equal or Increase or Decrease answers are i can give the treasurer what he seeks.
 

A:Excel Formulas

Read other 9 answers
RELEVANCY SCORE 56.8

I am looking for help with an excel formula. I'm not sure which formula I need. I have two spreadsheets. One is the budget for the year. The other is actual cost for the year. these are both by month. At the end of the actual cost spreadsheet I have a column to put year to date budget. I want to be able to compare the budget to the actual as I enter each month's actual. So I need the formula to only add the budget amounts to the month that my actual has filled in. Can anyone help with this???
 

A:Help with Excel formulas

Read other 8 answers
RELEVANCY SCORE 56.8

Hey guys,

I have a little problem with excel. I did allready research on the web but
nothing works.
I want 2 formulas in 1 cel but my percentage doesnt work.
You can see attached my excel file.
I want the winst/verlies and rendement in one cel.

Who can help me?
Thank you
 

A:Excel: 2 formulas in 1 cel

Hi Bellic,
Welcome to TSG. You can't display both a currency and a percentage in the same cell and keep the formulas. You can kind of do it for display purposes with something like this (I used $, but I know it's really euros, so you'd use the euro symbol):

="$"&J2&" "&K2&"%"

But you wouldn't be able to perform calculations with that cell. Is there a reason you need to display them both in one cell?
 

Read other 1 answers
RELEVANCY SCORE 56.8

I am using Windows XP Professional with Excel 2003 (11.5612.5606) there is windows service pack 3 installed at work. I am trying to create a file that will track percentages for emloyees. The pink highlighted ones are nights and the white is days, they have different formulas. The sheet is set for automatic updating but they are not updating the percentages. If you change the entered numbers then change them back to zeros, they don't enter 0%. I'm not sure it uploaded the file either. It says invalid file when I try to upload, it's 133 KB.

A:Excel formulas

can you zip the file up and then load - MAKE sure that no personal data exists - this is a public forum and you need to protect any data - so use dummy data

Read other 2 answers
RELEVANCY SCORE 56.8

I am wondering if it is possible to imbed a Vlookup formula inside of an IF formula.

I am creating a spreadsheet in which I have a column with dealer prices and another column with Retail prices. I want to be able to create a formula that allows me to pull up a value from either the Dealer column or the retail column depending on which radio button is pressed.

This is the formula that I have and it is not working for me please let me know if I am missing something
.
=IF(EndDump!$G$3=1,"=vlookup(EndDump!$G$2,EndDump,4)",=if(EndDump!$G$3=2,"=vlookup(EndDump!$G$2,EndDump,5)","")

Thanks,
Semduvidasinger
 

A:Excel Formulas

As far as I know this should be possible. However, reading this may help:
http://www.cpearson.com/excel/nested.htm

Looks like the syntax may be wrong, I don't think the = goes inside a nested function.
 

Read other 2 answers
RELEVANCY SCORE 56.8

Help! I dont even know whether it can be done in Excel XP. Every week I do a table for Sports results. Teams in random order with results of that game. ie Team a 6 - 1 Team b So team a won got 3pts for a win 1 for a draw and 0 for a loss. I have 18 weeks of tables for these events. I want to make another table to calculate the position of these teams.

Have 5 columns
Rnd, Date, Team, Score, Team

1,15/03/2003, Burleigh Heads, 6 - 0, Banora Point
1,15/03/2003, Merrimac, 6 - 0, Kingscliff
1,15/03/2003, Coomera, 1 - 2, Mudgeeraba
1,15/03/2003, Northern Stars, 0 - 1, Southport
1,15/03/2003, Murwillumbah, 6 - 2, Palm Beach
1,15/03/2003, Burleigh Heads, 3 - 3, Runaway Bay

2, 22/03/2003, Mudgeeraba, 1 - 0, Northern Star
2, 22/03/2003, Merrimac, 6 - 0, Kingscliff,
2, 22/03/2003, Coomera, 1 - 2, Burleigh Heads

I want to make another table from this one to calculate the points in a league system. So if after 2 rounds Mudgeeraba have played 2 games, won 2 games, scored 3 goals, had one goal scored against them, thus they have 6 points.
So to reflect these scores:
Team, Games played, Won, Lost, Drawn, Goals Scored, Goals Against, Points.

So every week I would like to be able to enter the results in the above table of the scores then these calculations are automatically entered into the results table, ending with a league table.



Hopefully this is a little clearer to understand. If its still a little confusing my web page is
http://home.austarnet.com.au/nellie/14div1

Cheers Sca... Read more

A:Excel Formulas

Hi, Scad.

I don't mean to be rude, but I cannot understand what you're asking and, likely, others can't either and that's why you're question hasn't been answered.

Can you:

Give us column headings.
Put commas into your explanation, because we cannot figure out where your information starts and ends.

and even REALLY helpful:
Save your workbook or a reasonable sample as a text file, and load it up here.
 

Read other 1 answers
RELEVANCY SCORE 56.8

Hi,
I'm trying to create a Summary page which is fed by a Data sheet which will be populated by multiple users. I've a couple of questions on how to show running totals which are dependant on multiple critieria.
On the Summary sheet I need to show the number of times a value is entered into a certain column (Column G) for each manager.

In addition I want to be able to show on my Summary sheet if the value in column I on my Data sheet has not been updated within 15 days of the date in column G. This must also be specific to each manager.
There are 7 managers on my Summary sheet and 200 rows on my Data sheet.

I'm sorry if I've not explained this very well. Please contact me if you have any questions.
Thank you so much for any help you can give me.
 

A:AND IF formulas for Excel

Hi, welcome to the forum,
A small sample with non-sensitive data and mention the version of Excel you are using will make it easier to answer
 

Read other 1 answers
RELEVANCY SCORE 56.8

I NEED HELP BAD on formulas in excel for Vista. Trying to make a spreadsheet of percentages off a total amount.
Example: in cell A... any # I key in it will x # by 2.8%= total in cell A. then cell B is cell A total x 17%= total in cell B x 18%= total in cell C= total in cell C x 65%=cell D

This will happen no matter what # I key into cell A. It will auto cal cell ABC&D

A x 2.8% B x 17% C x 18% D x 65%
I hope someone can understand what I am trying to do. HELP PLEASE ASAP!!!!

A:Excel Formulas... HELP

Hi Ginkle,
I can?t tell why Excel is auto calculating without more info. What were you originally trying to calculate?
Also, I would need to see what formulas you?re using. Could you submit a screen shot with formulas displayed?

HOW TO DISPLAY FORMULAS:
Press CTRL + ` (grave accent).
To switch back to hidden formulas: Press CTRL + ` (grave accent) again.

Fortunately, Excel is easy to work with, so it should be a simple fix

Read other 4 answers
RELEVANCY SCORE 56.8

Is there a way to view the formulas in the cell in order to print them out, rather than seeing the result?

Thanks!
 

A:Excel Formulas

Tools-Options, View tab.

Check Formulas.

This also allows for printing.
There's a shortcut for it, but it escapes me at the mo...
 

Read other 2 answers
RELEVANCY SCORE 56.8

Hello,

I am trying to add two formulas in an excel cell.

I have a work sheet that has the minutes spent on a job and the staff member doing the job.
I then have a list of names (significantly longer than the example)
Next to that list I want a count of how many times each staff member has spent over 180 minutes on a job.

ie. in the example sheet, J. Bloggs name appears three times, however on two occasions he has spent over 180 minutes on seperate jobs, so it should count as two in the far right column. if his name was to appear again having spent over 180 mins on another job then it would count as 3 and so on......

I can get it to count how often his name appears, i just cant do it to only count when the job is over 180 mins..... can anyone help?

Gary
 

A:Two formulas in Excel

Hi, welcome to the form.

Place the formula below in I2
Code:

=COUNTIFS(C:C,$H2,B:B,">180")

This will give you 2 for Bloggs, just copy the formula down for the others
 

Read other 14 answers
RELEVANCY SCORE 56.8

Looking for MS Excel help. 

A:excel formulas

Hi, For MS Excel Help, please hit f1. Regards.

Read other 1 answers
RELEVANCY SCORE 56.8

Hi I need some help with formulas in excel.

This is what I need and I cant figure out an easy way to do it, only manually.

ok so I have a spreadsheet already set up and i need to average a column, easy enought however i need to only have certain rows in that average.

column c has either the words SFR or Lot
column v has numbers

i need only the cells in column c that reflect "lot" to be in the formula for average on column v

How do I do this?

Thanks in advance
 

A:formulas in excel

something like this... =DAVERAGE(C1:V6,D1,C1:C2)
the C1:C2 has to reference the column name and with C2 contains the value LOT, you can just stick it somewhere as a reference, doesn't seem to let you hardcode the LOT value
 

Read other 3 answers
RELEVANCY SCORE 56.8

Hello,
I am trying to create a new spreadsheet for work that needs to have certain look up fields that return certain values depending on what options the user selects.

My spreadsheet columns are as follows:
A= Category
choices in category are - Cooker, Dishwasher, Washing Machine
C=Deliver From
choices in deliver from are - supplier A, supplier B
J = Installation cost (this would be £10 for cooker, £15 for dishwasher and £20 for washing machine from supplier A and £1 for cooker, £5 for dishwasher and £8 for washing machine from supplier A )

I am trying to make the spreadsheet automatically populate J (with the costs above) when A and C are both selected from the drop downs. So there are multiple outcomes.

So, if washing machine was selected and supplier A it would populate £20, but if supplier B was selected it would populate £8. Then if dishwasher was selected from supplier A it would populate £15.

Hopefully this makes sense and someone can help me! I have managed to do part of it but cannot do the 2 lots of choices in 2 fields.

I am using a mac

Thank you
 

Read other answers
RELEVANCY SCORE 56.8

I need some help plotting and graphing some equations that look like this,

y = e^(-x^2)

y = sin(x)

I have a few more, but you get the gist.

Any help would be greatly appreciated.

Cheers.
 

A:Excel Formulas

Sounds like homework. Did you do a search on Google? There seems to be lots of information about it.
 

Read other 1 answers
RELEVANCY SCORE 56.8

Guys,

I need help with a formula in excel.

I am trying to have the formula say

If A1>100 and A1<500, Display X if not leave blank.
I have the if(A1>100,"X","") down, I just need help with the syntax.

Help Please!
 

A:Excel Formulas

This should work

=IF(AND(A1>100,A1<500),"X"," ")
 

Read other 1 answers
RELEVANCY SCORE 56.8

I am working with two fairly large spreadsheets (different workbooks) and need to create a formula that will do the following (if possible):
IF B2 on sheet 1 equals a cell in column B of sheet 2
THEN write the value of the cell in column D (sheet 2, same row as match in column B)
IF no match, then write 1
I hope that this is understandable and I REALLY hope that someone can give me a clue.

Thanks in advance!!
 

A:Excel formulas

This will work
=IF(ISERROR(VLOOKUP(B2,Sheet1!B:B,1,0)),1,VLOOKUP(B2,Sheet1!B:B,1,0))
 

Read other 3 answers
RELEVANCY SCORE 56.8

First post-probably a long one. Sorry, username says it all.

An Excel file of 23 columns and ever increasing rows (currently 3000+) keeps track of data on customer transactions. Column T records each transaction's location name, column U records the location number, and column W records the sale price.

I'm creating stats of:
1. Top 15 regions by number of sales
2. Top 15 regions by sale amounts

There are hundreds of location numbers (formatted 000-000 or 0000-000) relating to regions. For example, 357-000, 780-000, 930-000, and 1446-000 are all main locations of region A. Additional loctions change the three digits after the hyphen; however, 357-003, -004, -005, belong to another region (meaning I can't just look at the numbers before the hyphen).

I used SUMPRODUCT to calculate the number of sales, but had to enter every single location in the formula to get it to work. And once another location is added to any region, that region's formula needs to be updated.

I used {=sum(if(u15:u10000="357-000",w15:w10000,0))} but it only allows me to add one location's sales totals at a time.

I even created another spreadsheet with all the location numbers and names to try to use VLOOKUP to at least standardize the names and go from there, but that formula =VLOOKUP(U2634,Locations.xls!$A$2:$B$370,2) is giving a result of #N/A.

I can't include a copy of my spreadsheet b'c the info is considered confidential. Please help! Please no VB sol... Read more

A:Excel-which formulas

Read other 7 answers
RELEVANCY SCORE 56.8

I am using the "Rate" formula to arrive at the
ineterest of a mortgae payment.

I enterred the ff:
MOnthly Payment ( principal & ineterst)
Number of total payments
Loan Amount

After entering the numbers, I get NUM#

What am I doing wrong?

Thanks for any inout!
 

A:Excel Formulas

Read other 8 answers
RELEVANCY SCORE 56.8

Hi all,

I'm sure this is the stupidest question ever but I've been trying to sort it out for about an hour, and I can't seem to get at the problem!

I'm filling in a timesheet made for me in Excel, with details of number of hours spent on each project over a period of days. At the end of each row is a total cell, with a formula in it that calculates the total. An example of this formula is =SUM(B16:AF16) where 16 is the row number. The formula works fine in some rows, but not in others - sometimes it just doesn't add up any of the numbers, sometimes it ignores all numbers below 1, so that when I've spent half an hour on something and entered 0.5 it just ignores the value. I've checked all the cells are in general format not text, and I know that the row numbers within each formula are correct - beyond that I'm baffled! Pleeeeeeease can someone enlighten me?!
 

A:Excel SUM formulas don't add up

Put a 1 in any blank cell.
Copy it.
Select all your hour amounts.
Hit Edit-Paste special, Multiply.
This forces Excel to see the cells as containing numbers.

It's just a stupid Excel thing...

If that doesn't work, send the file to [email protected]
 

Read other 3 answers
RELEVANCY SCORE 56.8

Need a formula for number of years worked. In other words, looking for a formula to take info from cell such as date hired through current date to =current years service. Any info creating formulas. Thanks.
 

A:Excel formulas

Use the datedif function

i.e. =DATEDIF(Date1,Date2,"Interval")

Where
Date1 is Start Date
Date2 is the end date
Interval is one of the following: You must use the inverted commas

"m" Months
The number of complete months between Date1 and Date2.

"d" Days
The number of days between Date1 and Date2.

"y" Years The number of complete years between Date1 and Date2.

"ym" Months Excluding Year
The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years
The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months And Years
The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.
 

Read other 1 answers
RELEVANCY SCORE 56.8

Just wondering how use formulas to find the average of a list of numbers. How can I find formulas to help with this.
 

A:Excel Help With Formulas

=average(a1:a10) will provide the average value of cells A1 through A10.
 

Read other 2 answers
RELEVANCY SCORE 56.8

How do I create an IF formula that has more than one criteria to produce more than one result? For example: =IF(A40 <1400,14)or IF(A40>1399 and <1600,13.75) etc.
 

A:Excel Formulas

You could just use "nested IFs", eg:

=IF(A1<1400,14,IF(A1<1600,13.75,0))

Personally I quite like a variation of VLOOKUP that dispenses with the actual lookup table :

=VLOOKUP(A1,{0,14;1400,13.75;1600,0},2)

HTH,
Andy
 

Read other 2 answers
RELEVANCY SCORE 56.8

Hi all

Please refer to the spreadsheet attached...

Can anyone tell me if there is a formula to find the minimum score & maximum score for each column? I know I could just enter it myself but I just want to know if such a formula exists.

Also, could someone please check my formula in the ‘Results’ column to make sure it reflects the following correctly?

If the students average is greater than 75, they should receive and ‘Excellent’ comment, between 55 and 74 is ‘Good’, between 45 and 54 is ‘Fair’ and anything below 45 is ‘More Work Needed’.

T.I.A!
 

A:Need help with Excel Formulas

Read other 13 answers
RELEVANCY SCORE 56.8

Anyone know how to set the formula for sum = + or - 5%? I can only do one or the other, but not both at the same time. Thanks muchly!
 

A:Excel If Formulas

Read other 6 answers
RELEVANCY SCORE 56.8

Hi,
Please check out this formula and try to correct it. (see attached)
thnks
 

A:IF formulas (Excel)

Read other 16 answers
RELEVANCY SCORE 56.4

Hi all. I'm on Vista and an Acer Aspire 5920G laptop.
A few weeks ago, my pc stopped remembering my username on my Hotmail account.
I didn't change anything and wasn't aware of downloading anything that would have changed the settings. I have tried to sort it out, but can't make it happen.
Other usernames arre remembered (the one on TechGuy for example).
I go to my emails quite often during the day and have a long username, so it's getting a bit boring.
Can anyone offer a solution??
Thanks in advance. Mark.
 

A:Remembering user name on hotmail/Auto Form Filling

Hi have you tried system restore to a date when it was working
 

Read other 1 answers
RELEVANCY SCORE 56

I created a great set of spreadsheets with formulas that were working perfectly. Now, when I enter data, the cell ignores the formula and nothing happens. This is true of all the sheets in the book. Help!!!!
 

A:Excel ignoring all formulas

Read other 16 answers
RELEVANCY SCORE 56

Attached is a excel spreadsheet. What I have been working on is a formula that would do the following; If sheet1 A2=sheet2 A2 and sheet1 C2=sheet2 C2 then copy sheet1 to sheet 2 for that row from D to J.

Hope I have explained it well enough.

Thanks,

Derald
 

A:Excel 2010 Formulas

Read other 7 answers
RELEVANCY SCORE 56

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.
 

Read other answers
RELEVANCY SCORE 56

I NEED HELP BAD on formulas in excel for Vista. Trying to make a spreadsheet of percentages off a total amount.
Example: in cell A... any # I key in it will x # by 2.8%= total in cell A. then cell B is cell A total x 17%= total in cell B x 18%= total in cell C= total in cell C x 65%=cell D

This will happen no matter what # I key into cell A. I will auto cal cell ABC&D

A x 2.8% B x 17% C x 18% D x 65%
I hope someone can understand what I am trying to do. HELP PLEASE ASAP!!!!

A:Vista Excel Formulas.. HELP

I am far from a spreadsheet wizard, guru, or whatever else you might want to say, but to the best of my knowledge a spreadsheet cell can basically hold a static value or a formula. So you cannot have a formula in a cell, and then have it take some kind of action based on some number you enter in, you would need a second cell for that.

So just for the sake of the current example, let's say that you enter your variable into cell A2, and A1 holds the formula. So you would have something like =product(A2, 0.028) then =product(A1, 0.17) =product(B1, 0.18) =product(C1, 0.65)

Read other 2 answers
RELEVANCY SCORE 56

Here is the scenario...
C10 is blank, F10 is 1

Using a macro, I would like to place a formula in C10. The formula is ...

=IF(F10=1,dateandtime(),"")

This is how I would manually add this formula if I typed it into the cell. However, the complexity of this spreadsheet dictates that I add it using VBA. I've played around with a variety of code and this is as close as I can get to making it work. However, it still bombs out.
Code:
Range("C10").Value = "=IF(F10=1,dateandtime(),"")"
Now I've determined that the problem is the "" in the actual formula. So if I change the code to this, it works.
Code:
Range("C10").Value = "=IF(F10=1,dateandtime(),C2)"
I'm not looking for C2 or a value of any cell. I realize this could be used as a work around, but can quotation marks be used in VBA for this scenario? I thought somewhere I seen &" be used, but that is not working either.
 

A:Solved: Excel, VBA and formulas

Well, well ... sometimes asking your own question sparks another idea to test. Typically, it is one of those solutions that makes you say "Duh".

***Double the quotations***

So for the next person searching for an answer ...
Code:
Range("C10").Value = "=IF(F10=1,dateandtime(),"""")"
This works correctly.
 

Read other 1 answers
RELEVANCY SCORE 56

I am working with a large Excel (2007) file, and my problem is that when I change data in cells that feed formulas, the formula answer is not changing accordingly. If I highlight the cell and then click on the formula bar and hit enter the formula 'comes alive'. But coming back to the file later, cells that I had revived this way were frozen again.

Ideas?
 

A:Excel formulas not updating

Hi, welcome to the forum.

If you press F9 do they refresh?

How is the calculation set, to automatic?
Check you Excel settings.
 

Read other 3 answers
RELEVANCY SCORE 56

Hi tech guys I am trying to figure out the formula to comapre date ranges in two columns. I work in procurement and I want to know if date in the first column as compared to the second column date is < or = 5 days. If the date is > than 5 days it would "not meet" our variance schedules. For Example: 11/2/11 and 11/5/11 Meets our variance date, however 11/2/11 and 11/12/11 does "net meet" the variance.

Can you please help?
 

A:Solved: What if formulas Excel

you could use conditional formating to cange the colour of the cell that does not meet - as well

the if statement would be

assume 1st column is A and second is B

Example: (A2) 11/2/11 and (B2)11/5/11 Meets our variance date, however (A2) 11/2/11 and (B2) 11/12/11
=IF( B2 > A2+5, "Not Meet", "Meet")

with dates if you add +5 that should add 5 days
depends if there is any time involved

i have added the conditional format as well in attached spreadsheet exampe
 

Read other 3 answers
RELEVANCY SCORE 56

I am trying to add "r" letter to a cell # formula.
I want to add how many rushes "r" a football player has with the yards he gained.

(c15:az15) find rushes that correspond to a player's number in cell (h32)

can't figure it out. Please help !!!
 

A:Excel spreadsheet formulas

Read other 14 answers
RELEVANCY SCORE 56

I am working with the US Naval sunrise sunset chart. The information I have imported into excel and each cell has a time
ie
1640

I created two formulas
=TIME(LEFT(a1,2),RIGHT(a1,2),) = 16:40:00
and I need to add 45 minutes to that number
=ab+TIME(0,45,0) = 17:25:00

How do I combine the two formula into one cell?

Gritzman
 

A:Combine two Excel formulas

Time(left(a1,2),right(a1,2),0)+time(0,45,0)
 

Read other 3 answers