Question about Microsoft Excel for PC

1 Answer

EXCEL The following formula will pick up values on a certain date,but when it goes through it again the next day it zeroises the contents of the cell. I know what it is doing, but what I want it do is to leave the contents if greater than zero and not zeroise when actioning the formula again. =IF(AND('Summary Totals'!$D$46-'Summary Totals'!$D$51>0,TODAY()=H5,),'Summary Totals'!$D$46-'Summary Totals'!$D$51,0)

Posted by on

  • 6 more comments 
  • pim99 Apr 24, 2008

    This caused a circular reference error, but thanks for looking. pim99

  • pim99 Apr 25, 2008

    Hi Smartsolve,



    The following formula looks at two values from today and produces a result, and records the results in the cell. What I need is that value to be kept in the cell and not zeroised the next time the formula runs. I f you leave the else 0 out it causes an error. What is really needed here is some code or formula to leave the cell alone if it has a value greater than zero in it. In this respect it would not matter if the underlying formula was removed as long as the value remained in tact.



    Hope this clarifies the problem further.



    pim99

  • pim99 Apr 25, 2008

    It looks great. I have not thoroughly tested it yet, but thanks for all your help.

  • pim99 Apr 25, 2008

    I have tried to run this macro but the system will not even recognize it exists, I expect because it is a macro that needs to be hyperlinked to something, and is not a function. Any suggestions

  • pim99 Apr 26, 2008

    Smartsolve,



    Thanks for all your help. What you did was fine as a macro that could be hyperlinked. This loaded up fine as a macro in VBA. What I was was really after was a piece of code (at the end of the formula above to replace the else '0' whereby the resultant values would stay intact and would not be overwritten with the else '0' when the formula ran the next day. I have tried various scenarios but most have resulted in circular references. Not sure it can be done!



    pim99

  • pim99 Apr 26, 2008

    Thanks for looking at it again. Although the macro seems the only viable option I really wanted to move away from this as it was implicit on the user actioning it, and users being what they are are prone to forget. It looks as if I will have to emphasize its importance on a daily basis instead of the daily automated save that I was hoping for.



    Anyway thanks again for looking into this.



    Regards,



    pim99

  • pim99 Apr 27, 2008

    The problem here is that I could have 365 days of individual data that I want captured and saved. In this respect I would not want the daily data overwritten. The only way to achieve this is to get the user to action a dedicated copy/paste macro at the end of the day. Of course this has the additional overhead of having to create 365/366 macros. This solution I regard as the sledgehammer approach, but in the absense of any formula code to get round this problem the daily macro seems to be the only solution. Unless anything more effective comes along I will continue to create the macros in VBA until reaching the 365/366 macro requirements and hyperlinking them to the day's calculated values.



    Regards,



    Mike Perry

  • archroom Jan 05, 2009

    i am a reasonable novice at this but i am trying to save an excel sheet via a macro, i want to save the sheet but want the name of the file to be the content of an individual cell ie, the date inputed in a cell or a name in a cell, anyone help??

×

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Legend:

    An expert who has written 200 answers of more than 400 characters.

  • Expert
  • 359 Answers

Add an IF statement that encloses the formula you've already written. Assuming the result is in one cell, called CELLREF for purposes of writing this for you:
=If(CELLREF>0,CELLREF,(------INSERT YOUR FORMULA ABOVE------),0)
Thank you for rating FixYa!

Posted on Apr 24, 2008

  • 7 more comments 
  • Cheryl Whalen Apr 24, 2008

    What is the address used for what we called CELLREF? It should be the cell that contains yesterday's result. In other words, you would have one cell with yesterday's calculation result (CELLREF), and the formula as written and modified above would reference that cell. Obviously, if you reference the cell you're in when writing a formula, you will get a circular reference, as a cell can't reference itself to calculate...



    Perhaps if I could understand what you are trying to accomplish it would help. Are you trying to get to a running total? I would be happy to go into a chat with you or to look at some spreadsheet (even test data, e.g., by e-mail) in order to help you write the formula. This is definitely attainable, we just need to clarify what you are trying to achieve. I am certain we can FixYa, as Excel formulas are one of my most prominent and serious areas. Let's communicate and get it done!

  • Cheryl Whalen Apr 25, 2008

    I would assume that the extra comma after H5 in the formula above is not really there in your sheet; otherwise the formula wouldn't work - ? Macro written for you, with a couple cells added to be able to more completely follow. The added cells could be placed wherever you like, and the macro would still work so long as the cell names are retained appropriately. So, OK, here we go:



    Cells are named as follows (to be able to trace and understand more easily in working with them):

    H5-DateToday // H6-DateRetain // D54-TotalCurrent // C54-TotalToday // B54-TotalRetain

    -----------------------------------

    Formulas in above:

    H5: Manually input current date (today formula could be used if you do this every day).

    H6: Blank; left to retain the contents of macro after running, so that date of today will be there tomorrow...

    D54: =IF(TotalToday>0,TotalToday,TotalRetain)

    C54: =IF(AND($D$46-$D$51>0,TODAY()=DateToday),$D$46-$D$51,0)

    B54: Blank; left to retain the contents of TotalCurrent to carry forward the amount for comparison the following day.

    ---------------------------------------

    In effect, what this will do is what you have asked, and I have tested it about 25 different ways - it always works. The total from today's calculation is copied to the TotalRetain field and the date is copied to the DateRetain field - so you know what has been carried forward and held constant for comparison. The macro must be run before closing it out for the day, so that it is set for the next day.

    -------------------------------

    Macro (which I named Retain - choose what you like):

    Sub Retain()
    '
    ' Retain Macro
    ' Macro recorded 04/25/2008
    '

    ' Select DateToday and copy to DateRetain
    Range("DateToday").Select
    Selection.Copy
    Range("DateRetain").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    ' Select TotalCurrent and copy to TotalRetain
    Range("TotalCurrent").Select
    Selection.Copy
    Range("TotalRetain").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    End Sub
    ----------------------------------------

    The macro will need to be pasted into the VBA window, and can then be run from within your worksheet, typically I would think prior to closing it. The formulas will work every day upon opening, assuming the macro was run when it was last used.



    We should not definitely be at a FixYa rating, don't you think?!

    :-) Thanks and good luck!

  • Cheryl Whalen Apr 25, 2008

    Sorry, pim99, I just looked at this again and noticed the typo at the end, it should read "We should now (not "not") definitely..."

    I am just hoping that since I did so much work to give you an absolutely workable solution, that you might be able to see your way clear to a FixYa rating. Of course, I will continue to help if you have any need for more assistance in how to accomplish the items I wrote for you. Thanks for rating FixYa!

  • Cheryl Whalen Apr 25, 2008

    Fabulous. Just let me know if there is any further need for help!

  • Cheryl Whalen Apr 26, 2008

    Did you name the cells as I indicated and set those up? Did you copy the macro code exactly into the VBA window, or how did you get it into Excel?

  • Cheryl Whalen Apr 26, 2008

    pim99:

    A couple of thoughts occurred to me through the night:

    1. The formulas I wrote and used in my test scenario obviously were all on the same sheet, and therefore did not reference another sheet (as yours indicates a sheet named SummaryTotals). If this is a sheet separate from where the formulas I gave you reside, that needs to be added in to the cell references.

    2. What exactly do you mean the system won't recognize the macro? Do you go to Macro - Run, and it's not listed? If so, I would guess that the macro was not placed into the proper VBA window -- OR

    3. What security levels are set in re macros? If the security settings are too high, no macros are permitted to run by Excel.



    Please let me know, and if there is a way for me to help to get it into the sheet directly, I would be happy to. You could also go in and record the macro directly, following the same steps after the new cells are set up. Waiting to hear back...

  • Cheryl Whalen Apr 26, 2008

    By the way, I obviously don't know your particular levels of expertise and so forth, but if you need help understanding the usage of functions, hyperlinks, macros, etc., I will be happy to help, but this will take time. Hyperlinks hop to another spot, basically, and do not perform calculations or make decisions. Functions perform specified calculations, and custom functions are also written in VBA. Macros are written in, and modified/edited in VBA as well, and VBA is a long and specialized area of its own. The easiest way to get a macro into a workbook, and have it do what you intend, is to record the macro from within the workbook. What I did was to do that for you, edited to make it more understandable and easy to follow, tested it some 25+ times, and then put it out there for you. So, I think at this point, we just need to determine why it isn't coming up, and this is just a process of elimination. It DOES work!

  • Cheryl Whalen Apr 26, 2008

    We could likely accomplish it with formulas, but it would definitely require adding more cells containing formulas that would basically just serve as "holding containers" and make the process much more complex. This would also very likely end up resulting in changing to manual calculation (rarely a good idea) and/or several steps becoming manual processes. All in all the cleanest, easiest way to follow the process is using the macro with the few added cells we have placed. Again, it is difficult without the spreadsheet, and as an "outsider looking in", but I spent a good amount of time on this, and I truly believe it is the best solution. Everything I could come up with involving formulas only either would not work, or was far too cumbersome, and had the disadvantages mentioned earlier. Although not much is really impossible, if the macro works, I would suggest to go with it. I will continue to think about this, and the only thing running around in my mind right now is a way to possibly use the "DateRetain" field as a qualifier in a nested IF statement. Let me know - smartsolve

  • Cheryl Whalen Apr 27, 2008

    Within the context of your needs, it might be better to have the macro run automatically when the file is opened - what do you think? This would do away with the concern re users forgetting, and would be no different from a function that operates automatically when the workbook opens. I think that even if we could get formulas to do it (and maybe we could with a bunch more time and added cells...), it would definitely be a much more convoluted process and weigh it down. Pleas look at this reference, and let me know before I take the time to write another macro for you. (I think would be better to use the open event VBA macro as opposed to autorun recording, if possible.)

    http://office.microsoft.com/en-us/excel/...

×

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

How to move the result of a formula up or down one cell


go google and type in --manual for excel version (what ever it is ) and down load the pdf file
go to a book shop and buy a Excel for dummies book
or e-bay and excel for dummies or other excel explained books
explaining it as a reply will be as confusing as the reply would be pages long

Oct 13, 2015 | Microsoft Excel for PC

1 Answer

How do i subtract two cells and progress down a row by a day so the next day the formula subtracts the current day and previous day?


You have to start the sequence with a slightly different formula because there is no preceding value for Monday.
25525374-qztzwsd5tae24twsq5kxbvm0-3-0.jpg The rest of the formulas are running totals so they are the same just dynamically duplicated down the column add infinitum.
25525374-qztzwsd5tae24twsq5kxbvm0-3-2.jpg Just duplicate the the formula Picking up the value form the result column and subtracting the next running subtraction value.

Mar 06, 2015 | Microsoft Excel 2010

2 Answers

What are the formulas in grading the grades uisng microsoft excel


Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.

Formulas used in Excel must follow a certain syntax.

  1. All formulas begin with an equals sign (=).
  2. Some formulas use operands such as +,-, *,/ for addition, subtraction, multiplication or division.
    For example, the formula =A1+A2+A3 would add the contents of cells A1, A2 and A3.
  3. Other formulas refer to different functions such as SUM, AVERAGE and others.
    For example, the formula =SUM(A1:A3) would add the contents for the range A1 through A3.
  4. Formulas can be combined with operands.
    For example, the formula =10*SUM(A1:A3) would add the contents cells A1 through A3 and multiply them by 10.
  5. Functions can be nested within each other.
    For example, the formula =SQRT(10*SUM(A1:A3)) would take the square root of ten times the sum of cells A1 through A3. When functions are nested, it is important that the number of left parentheses match the number of right parentheses.

Aug 19, 2011 | Microsoft EXCEL 2004 for Mac

2 Answers

Need to have a certain cell say OFF when it is a certain day


Use the test function.
Formula in cell A1 would be =text(a2,"dddd").
This would pick up the date from cell A2 and return the day of the week in cell A1.

Jun 06, 2010 | Microsoft Excel for PC

2 Answers

Need formula for excel


Well the symbol for greater than would be => and for lesser than would be +<  so if you can incorporate that into your formula it should work. Something like A2=>Sheet3.

Jun 11, 2009 | Microsoft Excel for PC

1 Answer

Formula for copy multiple cells at a time


You'll want to use the IF formula, its syntax goes like this: =IF(condition to be met,value if true,value if false)

If you want to use text for the true/false values, you'll need to put the term in quotes.

Example, lets say you want to know if 260+G$2+F60+$X$99+$A25 is equal to 1024, then the formula would be: =IF(260+G$2+F60+$X$99+$A25=1024,"True","False")

Dec 29, 2008 | Microsoft Office 2003 Basic Edition...

2 Answers

Formula for calculating the days beween months


for example, cell A1 has date (01-01-2008) and cell A2 has current date (08-24-2008) and cell A3 shows total days, is that you want to know? if yes, apply formula as under...

cell A3......(properties set as General to show digits)................ =SUM(A2-A1)

Aug 25, 2008 | Microsoft Office Professional 2007:...

1 Answer

Computer


Any formula in Excel starts with the equals sign ("="). This is how Excel distinguishes between formulas and literal values. Knowing this, you can create lots of formulas using the usual operators of +, -, * and / for addition, subtraction, multiplication and division, respectively. Please see the examples below.

Add 1 + 1: =1+1
Add the contents of cell A1 to the contents of cell B1: =A1+B1

Subtract 2 - 1: =2-1
Add the contents of cell A1 from the contents of cell B1: =B1-A1

Multiply 1 times 2: =1*2
Multiply the contents of cell A1 times the contents of cell B1: =A1*B1

I hope this resolves the question. If not, please let me know.

Aug 08, 2008 | Microsoft Computers & Internet

1 Answer

How to find day of particular date in excel


First type in 31-Aug-1974 in Cell = C1
then in the calculation cell use this formula

=text(C1,"dddd")

this yields Saturday in text

Jul 29, 2008 | Microsoft Excel for PC

1 Answer

Copying data from one sheet to another if two fileds match


Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):
A1: Part B1: Code C1: Price D1: Find Part E1: Find Code
A2: x B2: 11 C2: 5.00 D2: y E2: 12
A3: x B3: 12 C3: 6.00 D3: y E3: 11
A4: y B4: 11 C4: 7.00 D4: x E4: 12
A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:
=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:
=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Jul 08, 2008 | Microsoft Computers & Internet

Not finding what you are looking for?
Microsoft Excel for PC Logo

214 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2635 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18331 Answers

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...