Want to know more about how to use Numbers on your Mac?
MacMost Now 255: Using Spreadsheet Formulas in iWork Numbers
Comments: 105 Responses to “MacMost Now 255: Using Spreadsheet Formulas in iWork Numbers”
Comments Closed.
MacMost Online Courses
Keyboard Shortcuts PDF
Mac Video Tutorials and Tips for Apple Users
This is a great episode! It also works in iWork '08 if anyone is wondering...
Thanks for the simple introduction to spreadsheet formulas. I very much like the color coding in formulas for use with young students. One issue I can't seem to resolve, though, is how to handle a formula which uses the same cell twice. For example:
=B1 + 2*(A1 + B1)
While I can type this formula in directly, I can't get the second B1 by clicking on the cell- clicking on cell B1 takes me back to the first B1 in the formula.
Is this a bug or a "feature" that I don't understand?
Aha! I figured the work-around: hold the Command key before clicking. The icon changes to allow the cell to be used more than once in the formula.
I don't think much of this "feature"...
Yes, that's the trick. I guess the feature is the ability to find a previous reference to a cell in the formula.
Hi and thanks for all. I have a big issue (may be for me), if I want create a questionnaire and I want ask for example: how many employees you have? the answer is "5" is possible to create others five question list for each employee?
(like a repeating as many as the answer).
Not sure what you are trying to do. You could always sit down with someone at an Apple store and have them show you.
Thanks for sharing this information. I've been an Excel spreadsheet user but couldn't figure out how to create a formula in Numbers. Or more specifically, what to do when the calc box was displayed in the blank cell where I wanted the results to be displayed. Excellent job!
thank you for the tutorial ... its funny how the easiest things always are the hardest to find and figure out. Great Video.
Excellent tutorial. I went all over apple's site and couldn't find what I needed. Your video helped me figure out how to set up a simple payment tracker spreadsheet in minutes.
Question: I see how you do the sum for a long column; that seems straight forward but how would you do the inverse? That is to say if I want to start with a beginning balance of 1000.00 and subtract a series of payments from it.
I figured out how to do it with a few months worth of payments by picking the cell I wanted to be the equation and then subtracting the payment cell from the previous balance. b1(beginning balance)-a2(first payment)=b2(remaining balance); b2-a3=b3 and so on. This is slow and, if it were a long column, it would be tedious and time consuming.
I did look at the check book register template Numbers has but I couldn't really make sense of what they were doing. Can you help?
Thanks again for the excellent video!
You can copy and paste a formula down a series of cells and it will adjust with each copy. So copy =b1-a2 from b2 and paste it into b3 and it becomes =b2-a3. Copy and paste into a whole range (like b3 to b50) and you have what you want.
I am really confused as to how to set up my spread sheet so that I can enter truck heavy weights and subtract the light weights and get the actual weight of the product being hauled. I want to go across the spread sheet starting with B1 with the heavy weight and having c1 be the light weight and having the actual pounds in d1....but every time i try to do that it will not accept that type of formula. HELP I have worked on this until 2AM this AM and still am not getting a simple task to work for me.
Put this into D1:
=B1-C1
A tutorial on how to JUST add, subtract multiply and divide would be helpful. Getting in to copying or any thing else is a time waster. Having used Excell for years I couldn't find a tutorial that SHOWED division. Yes I see how to do it but like I said your tutorial above didn't show it. Seems Apple is more interested in showing the NEAT little things you can do to enhance the spreadsheet and leaving the basics out.
Interesting idea. I'll have to ponder that. So, what was the issue with division, exactly. As I see it, Excel and Numbers are exactly the same when it comes to doing basic things like division. What was the issue that you needed clarified to make the change from Excel to Numbers with division?
I am using iwork to create a spreadsheet. I need to find out how I can view a sum based on category rather than percentages that are shown in the charts?
I don't know what you mean by that. Can you be more specific? Maybe an example of what you want to do? Instead of posting here, start a new topic at http://macmost.com/forurm which it can be easier to discuss.
Hi Garry, Thanks for your tutorials, I have one problem I would appreciate help on. I receive a number or rentals from properties, I need to be able to calculate the percentage of various columns how can I set up a formula to carry out this task. Cheers, Marshall
Depends on what you mean. Percentage is usually just a division calculation multiplied by 100.
Hi Garry,
As an example I have a column of figures that add up to say £7,654.00 I need to show that a commission of say 10% which is £765.40 is being deducted and the final ammount, it would be useful if number would do this calculation.
Cheers Marshall
Use the SUM function to add up the column. Then, in another cell, multiply that by 0.1 to get 10% of that. Then in another cell, subtract the commission from the original total to get the remaining 90%.
Many thanks Gary will do
Marshall
Garry, nice tutorial but can anyone tell me how to work out VAT??? I have Gross amounts but i also need to show the VAT amount along with the net amount? Driving me crazy!
A gross amount of £117.50 is subject to VAT @ 17.5% leaving £100 Net. I have tried various uses of =sum but cannot nail it down.
You'll need to use algebra for that :)
If the gross amount is in cell B2, and the VAT amount is in C2, then B2/(1+C2) gives you your £100. I say 1+C2 because you want 100% + 17.5% which is really 1.175. So 117.5/1.175=100
Does that help?
Hi,
I am finally, after almost 30 years of Microsoft programs, finally getting ready to switch over to Apple products. The one thing holding me back is that I want to be sure I can adjust to the spreadsheet program. There is one maneuver I use soooo very often which is not available in MS Works or Open Office and it makes me crazy. I need it.
When entering a formula in multiple cells, I can highlight all of cells, enter the formula and hit Ctrl + Enter. The formula is then entered into all of the cells. Both MS Works & Open Office require me to enter the formula into one cell only, then copy it, highlight multiple cells, then paste. I know it doesn't seem like a big difference, but it feels like one when I have to do it over and over again.
I think Macs don't have a Ctrl key, but is there some way of entering a formula in multiple cells simultaneously which does not involve copying & pasting? Any information you can give me with this will be much appreciated. Thanks so much.
So, you are talking about iWork Numbers, right? Because you can just get MicroSoft Office for Mac if you want to stick with Excel.
As far as copying a formula to multiple cells: you can do it in Numbers, sure. You do copy and paste, but it is just one paste. You copy the cell. Then select a range of cells. Paste. The formula goes into all the cells. I'm not sure how that Ctrl+Enter thing works in Excel -- how does it know which cells to paste into?
Macs have Control, Shift, Option and Command keys. Windows has Ctrl, Alt and Shift keys. You typically use Command on a Mac like you use Ctrl in Windows (Command+C, Command+X, Command+V, etc).
Thanks for your quick response, Gary. Yes, I am talking about iWork Numbers. And I'm trying to get away from MS Office, that's why I was hoping Numbers had the same maneuver. It's pretty much the only thing I use frequently that I can't find anywhere else. In Excel, I highlight multiple cells, say F1 through F10, and enter a formula. Then I hit Control, Enter and the formula is entered into all the cells that were highlighted. (If I forget Control, and only hit only Enter, the formula only appears in the 1st cell that was highlighted.) Both MS Works and Open Office make me enter the formula into 1 cell only, then copy that cell, highlight multiple cells, then paste. It's just an extra copy/paste every time I want to put a formula in multiple cells, but I really don't like it. That's why I hoped Numbers had a similar sequence that allowed entering a formula into multiple cells simultaneously. It seems like such a cooler program than Excel, I'm really surprised this doesn't exist in it. Maybe it soon will? I can't be the only person who hates going through an extra step. :)
Hi, Gary
Thanks again for taking the time for this tutorial and all the formula assistance.
Here's my question: I'm trying to track a personal budget with loan & bill payments engaging a debt snowball... (paying off the smallest bill off, then adding the min payment from the first debt to the 2nd debt, and when that's paid off, adding that to the 3rd debt, and so forth). This is across 2 worksheets. Let's call the first one W1.
W1:
Column A has the name of the bill / loan
Column B is the remaining balance
Column C is the minimum payment
Column D is the new payment once the previous bill has been paid off
Column E is the number of minimum payments remaining
One a second worksheet (W2), I have a monthly tracking of my bills. I adjust the remaining total (B) of W1 as the amounts get paid off, which currently adjusts the payments remaining (E).
What I would like to do is once the total amount (B) hits 0, I would like the minimum payments on W2 to adjust to the new minimum payment.
I just don't have enough experience with the different formulas & syntax to put together a formula for varying situations. I have a feeling this may be a IF THEN ELSE type of situation.... but don't know where to go from here.
I'd like to know is how to create a formula that says in the correct syntax:
If debt = 0, then display new minimum payment, else display the regular minimum payment.
Also, if you have any advice on any tutorials / books /videos, etc. on Numbers formulas, I'd greatly appreciate it.
Matt
I'm not sure I understand what you are trying to do. Probably one of those things where you need first-hand help. Can't you just do this manually? Just change the one cell manually when you need to. That would seem simpler than any special formula that only comes into play once in a while?
I'm not sure. Perhaps a spreadsheet expert would know what you want.
Yeah, I know, but I was using this as an opportunity to really get into Numbers, and just got stumped on this. I'm tracking my debt repayments, and want to be able to forecast how many remaining payments / months before a debt is paid off, for ALL my debts. I'd like to create a spreadsheet that updates the other columns based on the total remaining balance of a particular debt.
Any thoughts on resources that teach about formulas and correct application / syntax?
Matt
There are tons of books on spreadsheets. But I think your need might be too specific for it to be answered in any one of those. I would try to figure out who in your circle of friends is good with spreadsheets -- or maybe someone once removed. Buy them lunch and talk through the problem.
Wow, this tutorial was so helpful! I was banging my head against the wall trying to figure out how to create formulas across multiple pages and you just showed me reason number 1,001 why mac is so awesome! I think too many years spent expecting things to be complicated on a computer, sometimes makes it hard for me to remember just how easy life with a mac can be (and is). Thanks again Gary! :)
Great stuff! I'm also looking to figure out how to automatically make months, weeks and days as 3 headline rows. ie; each month cell contacins the number of cells needed per day in that month, and week cells as well. I can in Excel but Not in Numbers - ???
I don't understand what you are looking for.
HI Gary, I am using numbers to do my books for my business. I need to have a running balance. Business costs in col. D, personal costs in col. E, deposits in col.F and balance in col. G.
Not sure how I can help. The best thing to do is to learn the basics of using spreadsheets -- maybe a good basic spreadsheets book?
I am tracking work time in and out of the city for city wage tax. I need numbers to separate. I guess I need to know how to use the "if" function. What do I have to do to calculate. Total hours, hours in city, hours out of city?
Don't see why you need to use the IF statement. Just have one column with hours in city, one with hours in city, and then one that is A+B. Then at the bottom of the rows, use SUM at the bottom of each column to sum up hours in city, hours out of city and the A+B column for a total. A pretty basic table. See episode 475: http://macmost.com/adding-up-amounts-in-iwork-numbers.html
I put start and finish times and it adds the total times. It seems as though it will be more work if I have to make more Columns. Is the if then function possible?
I don't understand what you would intend to do with the IF function that would make this easier.
It is a lot of Data that has to be entered. I need to know if it is possible to have it separated without making two different columns. I can email you a sample if you need to see it.
Probably best to find a spreadsheet expert and consult with them about it. The way I see it is if there is a lot of data, then there is a lot of data. The spreadsheet can't make the calculations unless it has all that it needs, you know? But clearly I'm not understanding what it is you need here. Best to have someone sit down with you and work it out.
I'm trying to do a simple accounting ledger style table in numbers and therefore need the totals from column c posted in column d on the same line as they are entered. For each cell in D I've formulated the sum from the same row in C up to the beginning, but I want the totals in d to only display for the row I've entered my numbers into. For example, C1-6 have numbers in them and the totals are correctly showing in D1-6, however in D7-25, where I haven't entered input into the corresponding C columns, The D cells are showing the same total from D6, where my last entry was put. Can you help?
Just try clearing out D7-D25. Copy from D6 and paste into D7-D25 again. See if that works.
Gary-
I have a tough one (and I've already been to the Apple store, and they're stumped). I want to use checkboxes in a spreadsheet I've created. Essentially, I have four columns of checkboxes... each column would be linked to a separate table below. What I want to happen is, when a certain checkbox is checked, the info from certain cells in the row (street addresses actually) automatically populates the same cells in the linked table below. Sounds easy enough, but we've been working on it for days... Any chance you can provide some assistance with this one??
That is a tough one. I'm not surprised that the people at the Apple Store didn't know -- that's a pretty high end spreadsheet question. It would be like asking a car salesman something about the engineering of the engine.
I would stop looking at this as a Mac Software Problem and start looking at it as a Spreadsheet Problem. See if you can find someone that is an expert at spreadsheets (Excel, etc).
Gary,
How do you subtract times in Numbers......eg I have two column......one is "OUT TIME" the other "IN TIME".........The times are eg "OUT" 22:15PM, "IN" 01:23AM....???
TIMES WOULD ME LIKE ZULU TIMES....
Thanks
G.
You just do it. I entered 10:00 into one cell, then 12:00 into the next one, and then created a formula subtracting the second cell from the first. The result was "2h" -- and I didn't have to do anything special.
Hi Gary-
I'm trying to get the average run for a given value in a particular column. for instance if the data was: 1,1,1,2,3,1,1,1,5,5,4,4,1,1,1,2,3,2,1,1,1,2,1,1,1
and I was looking for the average run of the number 1 the answer would be 3.
I found this: =AVERAGE(IF(FREQUENCY(IF(A2:A100=1,ROW(A2:A100)),IF(A2:A1001,ROW(A2:A100)))>0,FREQUENCY(IF(A2:A100=1,ROW(A2:A100)),IF(A2:A1001,ROW(A2:A100)))))
which works for my friend in Excel on his PC, but I can't seem to get it to work in Excel or Numbers (my preferred program) on my Mac. I'd rather not count manually because I have thousands of numbers to get through. Do you have any ideas?
Thanks
Liz
That's an unusual formula. I don't have any insight. I think it is just a matter of digging into the Numbers formulas and reading carefully what they do and coming up with a solution. I don't know the solution off the top of my head.
Hi Gary!
I did a worksheet for my friend with ots of formulae and charts.I am not sure if he has a MAC. So, when I tried to save it(tried export too) as excel there seems to be warnings that all formulae could not be exported...What is the solution.
I have used simple functions like IF , AND, MIN ,Between ,Sum etc...
Thanks!
Vid
What did the warning say exactly? Did you try it out on his computer with Excel? I'm sure that the formulas were exported, it was just a warning that some may not work the same.
I am trying to subtract C82 from C81 and put the answer in C83. How to I do it.
I have the PDF about formulas and functions, but I still can't figure out how to do this simple procedure. What is subtracting called in the function and formula PDF?
Thank you.
In C83, simply put the formula =C81-C82.
You can do it by typing =, then clicking on C81, then typing -, then clicking on C82.
It adds instead of subtracting. C 81 is the total of a column. Is that part of the problem?
Sounds like you have the wrong formula in there. Perhaps try to see if you can get someone with spreadsheet experience to help (Excel or Numbers use basically the same formulas).
Is there a way to subtract C81-C82 in D82?
Yes. Same way. Put the same formula into D82.
This is my formula and it still adds. =C81-C82
Hello Gary,
I don't think it's possible, but here's my desire:
Say I am given three values from a printed receipt of sales in my unattended flea market booth. I want to make sure that their math is correct. I want to simply enter the numbers they give me, but then have Numbers change the font color to red if the numbers don't add up. For example:
I have a positive value in A1 (sale).
I have a negative value in B1 (commission).
I have a positive value in C1 (sale - commission, or A1-B1)
Now, I want the the number in C1 to turn red in color if it doesn't match the sum of A1 - B1.
I can easily have the color of C1 change to red if it doesn't match A1, but having the color of C1 change based on (A1+B1) seems impossible. The formula editor doesn't seem to allow selecting more than one cell :(
You say "I can easily have the color of C1 change to red if it doesn’t match A1" -- how? I don't know what you are using to do that. Maybe I can find a way to extend that if I knew.
Otherwise, why not create a 4th column. Use that to calculate the difference. Then match C against that column.
Oh...in the conditional formatting box, I click on C1 and then choose "Not Equal To", and then click in the long formula box directly to the right, and then click to choose A1, and finally change the formatting to red. It will apply a rule that simply changes the text color of the value of C1 to red if it doesn't match A1. What I would like to be able to do is, instead of only being able to choose A1 as the "not equal to" selection, I would like to choose (A1)-(B2). Unfortunately, Numbers doesn't let us use a SUM as an option for a conditional. :(
Conditional Formatting, I see. In that case you just have to create another column. So you have A1, B1 and C1. D1 would have the formula =A1-B1. Then you can use conditional formatting on either C1 or D1 to change it to red if it is not equal to the other.
Another option would be to put a formula into D1 that puts a message ("ALERT!") there if C1 isn't A1-B1 and a blank there ("") if it is fine.
AH! The 'alert' trick would work great! Could you give me a sample formula? Thanks!!!!!!
=IF(C1=A1-B1,"","ALERT!!!")
Perfect. Thanks! I just changed the "Alert" text to be a big "X", and changed to font color of that column to be red. That way, I can have a nice, tiny column inserted in various places that will give me a red X when there is an error. Thanks again!!
Hi Gary, I hope you can help me with formatting IF, THEN statements.
I am using % increases for metrics in my business. I come across one particular metric that frequently = 0 from Last year, and makes dividing constantly undefined.
here is my if, then Question for block C6
IF B6=0 and B5>0, Then C6=100
IF B6>0 and B5=0, Then C6=-100
IF B6>0 and B5>0, Then C6=((B5-B6)/B6)*100
How do I write that? Any help is greatly appreciated. Thanks
I actually figured out my last question. But my new question is:
Using the sample above, can I abbreviate it by saying IF(B6=0 and B5=0,"-","•")?
I would like to use more than one criteria to display a certain output. I figured out how to write the last problem I asked, but it turned into a parenthesis nightmare.
Also is there a way on if statements to display a different color for the True and False outputs?
Thanks again for the help.
When you are creating a formula, there is the "Fx" button in the toolbar. Click on that and you have tons of info about what is available. In this case, look under logical statements for AND. You'll see that the solution is:
=IF( AND(B6=0,B5=0), TRUE, FALSE)
For coloring, you don't do that in formulas, but you can set "conditional formatting" for the cell in addition to the formula. Format, Show Conditional Format Rules.
Thank you very much!
Hi Gary,
I have created a (election) spreadsheet that has three columns of amounts- yes(E), no(F) and abstain(G). Each one of those columns gets a number when I receive a ballot. Then I have a fourth column(H) that is conditional- a box that is checked if the ballot is eligible for the election. How do I write a formula that calculates only the sum of yes votes(E) that have been checked in column H?
Use an IF statement in a new column. It gets a 1 if E is yes and H is checked. A 0 otherwise. Then sum up this column.
Gary,
Thanks for the quick response.
There may be more than 1 vote in the E, F or G columns, none of which should be counted if the H isn't checked.Is there a way to say for column E: sum of only numbers in column E where H is also checked?
Yes. That's exactly what I am suggesting. Use the IF function. See episode 557: http://macmost.com/if-statements-and-checkboxes-in-iwork-numbers.html
Thanks!
Gary,
If I have this formula:
=IF((b2="Yes"),40,0) The formula won't recognize yes or no. Is there a way to get the formula to recognize a string of text?
Thanks,
Matt
That exact formula works fine for me.
I've been banging my head against this wall for a while and wondered if anyone knew the trick: If I have six columns of figures representing daily results (e.g. rows show days, columns show say Area A sales, Area B sales etc). Each new day, a new row is created. At the bottom of the spreadsheet I have a row that shows say Amount to Target and is simply Target Value-Latest Result.
What I need to know is how to get the latest value from a column of figures to use in my formula without needing to manually update references each time a new row is created. It wouldn't necessarily be the max or the min as figures go up and down.
Thanks in advance!
Use footer rows. The footer row should have the sum formula. Then create a normal row above that and the footer row's formula will expand to include that new row. See episode 475: http://macmost.com/adding-up-amounts-in-iwork-numbers.html
How do I do percentages like taxes? I have a cell that is a subtotal and I want the cell below to multiply it by 0.13. I'm new to Numbers and formulas. Could you please write an example of a percentage formula? Thanks.
Just do what you said: multiple it by 0.13. So if the cell above is F72, then the formula is F73 would be =F72*0.13
Thanks for your quick response Gary. For some reason when I do that, the following is displayed in the cell: 18,668.00%. I find that result quite confusing.
It sounds like you have changed the formatting for the cell to a format that uses percentages. Simply change the format back to a standard number format.
Thank you!
I want to subtract two dates to obtain the number of days between those dates. I have used =DATEDIF(A7,A8,"D") but the number of days is incorrect. I am using the UK system of dates, ie dd/mm/yy, and have formatted the cells using this date system. The result of the calculation has obviously been done using the American system of dates, ie mm/dd/yy. Whilst I would prefer not to have to use this system (being British, I find it confusing!!), but I did try to change System Preferences to re-format the date, but it didn't seem to make any difference. Do you have any advice please.
You don't need to use a function for that. If you have two dates in A1 and A2, then simply =A2-A1 gives you the answer.
The date format doesn't matter. The date is either right or wrong. Change the cell formatting to show month, day, year (January 18, 2012) to check to make sure the date is right.
Hello Gary,
I'm trying to do an employee schudule using the template from Numbers and i'm having trouble with the formulas getting messed up when i try to change the time, for example I have people come in to work at 4:30 but there is no option, so when I change it messes up the total hours worked and so on? Do you have any ideas?
Thank You!
Joe
You just have to re-check everything. Look at the formula and run it through in your mind. If you are still having trouble then perhaps seek out some first-hand help from a friend that know spreadsheets.
Great videos, Gary! Thank you. I have created a Numbers receipt, a Numbers chart for inventory totals by day, month, year. And, a Numbers end-of-day sales total sheet with additional tax, inventory use, etc. type totals. Is there a way to get them to work together? I am currently gathering totals in my receipts and manually putting them in the sales sheet, then separating specific data into separate charts. And can you create a numbered receipt that numbers automatically?
Have you seen episode 258? That may help you: http://macmost.com/using-multiple-tables-in-iwork-numbers.html
Gary - Just started back using Numbers (lots of old experience with Excel) but can't figure out how to create a sequence. Want a column with 1, 2, 3 etc to 100 - in Excel (I thinks I remember) select the first three number cells, continue dragging until cell "a100" and select "fill down" - Anything like that in numbers?
Would also like to create a sequence with dates.
Thanks
You can do that in Numbers, yes. Put 1 in cell B2. Put 2 in cell B3. Then select those two cells. Grab the dot handle at the bottom right corner of that two-cell selection. Drag down. That will fill the cells under it with 3, 4, 5, 6, etc.
This video was VERY helpful, however, I have all of my cells formulated for a sum, except for one. it gives a a red arrow pointing upward and says "the the operator "+" expects a number, date or duration, but c5 contains a string." I've entered the same formula on 20 different cells and this is the only one that does this. Any help would be greatly appreciated.
Delete the contents of that cell (the one it thinks has a string in it) and re-enter the number. Or, change the format of that cell from String to Number.
Hi Gary, I am using numbers for my business, where I use it a lot to prepare pro-forma invoices. Now in a part of my pro-forma invoice, I want a cell to reflect the amount in words. For example:- $100 as Hundred US dollars or Hundred dollars. How can I do this?? Your help in this will ease my work :). Thanks in Advance buddy.
I've never seen any way to do that. Of course I've never needed to do it, so I haven't looked for it before.
Hi, wanted to mention ur vid was awesome! Was hoping to get ur expertise on what I think is called an IF formula.
Basically I work as an X-ray hand in nondestructive testing of pipe sizing of 2" up to 18" welds. I would like to have individual cells that tell me how many 2" or 3" welds I have examined through out the day. But I want to enter all the different sizes in one column and have it automatically total it in specific cells that I have labelled 2" in one, 3" and up in others. Let's say i enter five 2" and ten 3" in one column then it would allocate the total 2" amount in the cell labeled 2" and allocate the totals of 3" so i would know at the end of the day when ive examined 50 welds , I would know the exact totals of each different size weld.
Hope I've explained this properly. Any aid would be greatly appreciated.
You can do it many different ways. Use the IF functions to determine what is there. Or maybe have a column for 2" and another for 3" and make then checkboxes. Then data entry and totaling are easy.
I want to total monthly figures. So, for example, column A is dates, column B is the amount earned on that day. I might have 10 earning days in April, 15 earning days in May and 18 earning days in June. How can I show, in a separate column, the totals for each month. So for example "IF" the date column shows a date in April, include the cell showing the earnings for that day and make a total only of the April earnings, and put it "here", then "IF" the date colmn shows May, add up all the May amounts and put the total under the total for April and so on for the rest of the year.
Look at the Numbers Functions browser for SUMIFS -- it has an example that pretty much what you are asking.
Hi Gary,
Lots of great info Thanks!
If row C4 shows $14.25. I would like to add 50% to that and have the result ($21.37) shown in row in G4. Can you provide a formula please?
Cheers
Cell G4 would have the formula =C4*1.5
Hi Gary,
I tried to look and see if you already answered this if you have i missed it. What i am looking for is a way to have 31 sheets that i collect daily information the same every day and then a MTD cell that collects the information in that cell across each day and adds them up. What is the formula i would use? Thank you in advance for your help
Not sure what the complexity would be. If you just want to add them up, then use + operations to add those cells. Maybe consult with a spreadsheet expert if it is more complex than that -- you usually have to see the situation to come up with a conclusion.
For my accounts I have a column for money in, one for money out and a third with a formula for adding and subtracting these to the previous balance to keep a running total. All fine except I'm not adding info in date sequence and when I try to sort the sheet into date sequence it loses the formula in the total column and therefore messes up the running total. I haven't had this problem before with either appleworks or excel. Any advice?
You need to use Header and Footer rows. See http://macmost.com/numbers-headers-and-footers.html