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

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

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

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.

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?

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

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

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 answersHi 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 :)

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

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.

Hello,

Have you scanned your computer for malware?

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?

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

Read other 3 answersHi 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 :)

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

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

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.

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

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.

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!

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

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]

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

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

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!

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!

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.

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

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.

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

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

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

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

This will work

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

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

Thanks!

Tools-Options, View tab.

Check Formulas.

This also allows for printing.

There's a shortcut for it, but it escapes me at the mo...

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!

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

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.

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.

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.

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

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

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

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

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

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

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?

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.

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

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.

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.

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

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.

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

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

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

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.

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

I HAVE TO FORMULAS THAT I NEED TO ADD TO ONE CELL BUT NEED THEM TO INDEPENDENTOF EACH OTHER

=IF(AND(G4="DARK RED INCLINING",H4="GREEN ABOVE RED",I4="GREEN"),"LONG","NO TRADE"))

=IF(AND(G4="DARK GREEN DECLINING",H4="GREEN BELOW RED",I4="RED"),"SHORT","NO TRADE"))

AS YOU CAN SEE IN F4 AND F2 WHAT IM TRYING TO DO

1 MORE THING HOW WOULD I THEN MAKE F COLUMN DO IT FOR ALL CELL WITH OUT CHANGING THE INDIVIDUAL ROW NUMBER IN THE FORMULA

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

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)

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

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.

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.

I am trying to work out some excel formulas.

I have a racing database and currently I manually go through at regular intervals and do calculations such as the average winning time for a particular grade of race, at a particular track, over a certain distance.

I have all the historical data stored in a separate sheet. On my working sheet, I have a cell (which I use in other formulas) into which I manually insert the average time as above.

Is there a formula that I can insert that will give me the average winning time. The columns in my historical data that would be relevant are:

FINISH (so I currently filter to WON)

TRACK which contains the names of various tracks

DIST with various distances for all tracks

TIME which is the winning times

I have a pricing spreadsheet that I am working with and sometimes when I try and do a sum of three different column totals which have already been individually added up =sum(B2, C2, D2) as an example, Sometimes, I will get a blank cell after hitting the enter button and sometimes I will not. Does that make sense?

Tech Support Guy System Info Utility version 1.0.0.4

OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit

Processor: Intel(R) Core(TM) i5-4440 CPU @ 3.10GHz, Intel64 Family 6 Model 60 Stepping 3

Processor Count: 4

RAM: 8064 Mb

Graphics Card: Intel(R) HD Graphics 4600, -1984 Mb

Hard Drives: C: 452 GB (232 GB Free); F: 931 GB (690 GB Free);

Motherboard: ASUSTeK COMPUTER INC., B85M-E

Antivirus: Symantec Endpoint Protection, Enabled and Updated

not really

sum(B2: D2)

is the same

OR

=B2+C2+D2

see what they give you

Sounds like the Auto calc is maybe doing a lot of work and not quite summing

whats in B2, C2 , D2

a number or another calc ?

perhaps upload a sample sheet here

Hello,

Is there any way to "embed/fix" a formula in a few cells within a worksheet and have it applied to all worksheets with a workbook?

Basically, I have a blank template and it's being copied each time there's a new entry. The problem is there are formulas within that a not so savvy Excel user is deleting and wondering why nothing is totaling correctly. Originally, I had protected/hidden those certain cells and that worked perfectly except for when you copy the blank template the protection doesn't stay.

Any ideas to how I can "protect" these formulas?

Thank you,

Chloe

Just an idea here, not sure if it will work but if you include in your template a macro in the workbook open event that simply protects the workbook that might solve your problem.

Hope someone can help me. I have a formula in excel to give the percentage of various cells in a pivot table. I need to throw blanks if there is no data... instead of 0.00%

It's actually 2 cells - side by side. First cell will throw 0.00% based on the formula, the second will throw 100% based on the first one having 0.00%.

I want it to just go blank - no 0.00 or 100.00

Any ideas?

I have attached a screen shot. And the sorting... the cells that are giving me trouble, are not part of the pivot table - they are cells with formulas based on the pivot table.

Hi,

This may be a very elementary question. I am not a super sophisticated excel user, but was wondering if there is a way in Excel to compare the formulas (instead of the results) across several worksheets in a workbook. For example, I need to know if the formula in cell A1 on every worksheet is the same.

Optimally, if one does not equal the test formula, I would like to have the tab name returned.

Thanks in advance,

DV