Need help with a formula

Well, might as well try to get some useful stuff done while waiting for the update. I have a Spreadsheet in Numbers, that looks like this:

IF($C5=F$3,100÷F$4×$B5,“x”)

It is used to take any amount of money in a specific currency and convert it to dollars, like this:

B5 would contain the number - like 9886 Philippine Pesos, but only 9886 would be in the cell.

C5 contains a code for the currency, such as “P” for the Philippines (I have one column for each currency)

F4 holds the conversion value. It is based on how many Philippine Pesos are equal to $100 in U.S. dollars.

So the formula takes the foreign currency number and by multiplying 100 (the dollar reference I’m using), it divides it by the conversion value for that currency. So if I enter 9886 for Philippines Pesos, it divides $100 dollar base I’m using by 4943 and multiplies the result by the value I entered (9886) - which will result in the value of those Pesos in U.S. dollars ($200 in this case). Okay, all this works just fine, but I wanted to add something to it and, while it shouldn’t be hard, I’m just getting confused with all the “If” stuff!

That I want to add in column D (D5 to start), is a code to tell me it is a special case where 30% will be removed. So if this is the case, I’ll put an “S” in that cell. If it is not the case, I’ll leave it blank.

So, I need help adding this aspect to my formula above if anyone can help me. In short, if the S is there, I want the value of my original formula to multiply the result by 0.7 and if the S is not there, it leaves it as is.

One other thing. In order to make it easier to change these codes later, the “S” code will actually be permanently in cell D3 (thus I can just change that code if I want without affecting the formula).

So it is just like my currency codes I’d enter in column C that also reference the codes I have all across row 3.

Hi,

If I understand well what you need, you can simply add an if() in your formula that generates a multiplication factor:

IF($C5=F$3,100÷F$4×$B5xIF(D5=$D$3,0.7,1),“x”)

Thanks, that basically worked. I did needed to adjust a reference with “$” in the right place to make copying it to different columns work right.

This is a big help. Now I just have one more thing to try to figure out, but I’ll wait until tomorrow to see if I can figure it out.

Well, looks like I need more help. I have everything I really need working now, but there is one more “nice to have” thing I don’t have.

In column A, starting on row 5, I have names going fown.

In column B, I have an amount they gave. Some of the names will repeat if they give more than once, which is usually the case.

What I would like to do, in a column further to the right, is set up formulas that take each name, just once, and in the next column have the TOTAL they gave.

Here is a screen shot of the basic data

56%20PM

Seems to me I’ve seen this done, but I’m not even sure what the technique is called.

Hi

If you have a predefined list of names you can use SUMIF(). The syntax is SUMIF(A:A, name, B:B). The “name” can refer to a cell with the names so you don’t have to fix a name in the formula. Small warning is that it won’t work if the spelling of the names in column A is not exactly the same as the one in formula, even if it is just an invisible space at the end.

Otherwise a more elegant way is to use pivot tables but that’s possibly overkill for this exercise.

So, if I follow this correctly, I could pre-enter names in a column (and if a new person shows up, just add them to the end of that list), then run the formula in the next column, right?

If so, that seems easy enough - though I may research pivot tables to. They are things I hear about all the time, but never got into how they work!

Thanks again! I’ll work on this later tonight or tomorrow.

That’s correct.

Pivot tables are not madly complicated. You’d just need to give a name to each column of your data, say “names”, “amount”, … then select insert pivot table -> select the whole table of data (including headers) and that will create an empty table. On the right of the screen you’ll have the fields with the names you gave to your columns and 4 areas that define your table. You can then drag the field you want where you want it, so for your use case drag “names” in the row area and “amount” in the column area.

Thanks. I have done the first method, which will probably be just fine for my needs, but I do want to get around to trying pivot tables too.

I also set up a double check - getting the sum of the totals in this new section so I could see if it was equal to the sum of the other, individual, parts. At least that way, if I missed something, I’d know it - and I did! :slight_smile:

d

You just need map/reduce …

3 Likes

Seems there are no actual pivot table option in Numbers. Oh well.

I made another improvement. I found a formula where, by adding another column (which I’ll normally keep hidden), I get a number in this column for the first time any name shows up in column A (this number is in column L for now).

Then, in column M, instead of manually entering the names, I have a formula that enters whatever name is in Column A, but only if column L has a number. This was weirdly difficult to get to work and I’m not sure why some things I tried didn’t work. But this is what I ended up with:

IF(L5≠"",A5,"")

Then in column N, I had the total money given, just as before, except I had to make an adjustment to it wouldn’t print “0.00” if there was no name! This also gave me unexpected trouble, but this is what I have that seems to work:

IF(L5≠"",SUMIF(A,M5,K),"")

So, this is pretty good and keeps me from missing any names with the manual approach. There is just one problem. Any names that appear on more than one row, the 2nd and later occurrences leave gaps in column L and M.

Hopefully I can create another column which could consolidate these into a list with no blank rows - then I could also hide the column which has those gaps!

I’ve made a lot of progress on my spreadsheet and, al happens so often, every time I get something working, I find something else I want to do to make it better. Which led me to needing one more formula which I just can’t get to work. The attached spreadsheet’s relevant points are this (All starting on row 5): Column A: Numbers the first donation made by each person. I don’t think it matters that they are consecutive - that’s just how the formula I used worked. Column B: Names

Column N: The money, after conversions from different currencies, that I need to put in the section I’m having trouble with. Column O and column P: This is what I want EXCEPT for all the blank lines. If I can get the formula solution I need, these two columns will be deleted as unnecessary. Column P contains the total given from column N for each person. Column Q: This I a better version of column O (because it has no blank lines in between).
And now the problem - Column R. I can’t figure out how to get the numbers in the correct places. It does no good to eliminate blanks between names if they still exist in the total money given for each person! I tried all kinds of variations of the formula in Column P in the first 6 cells and you can see it isn’t working. Here is
It seems there should be a variation of the formula in column P to make it work in column R, but I’m missing something! Anyway, here is the formula for column P, in case that helps!: IF(A5≠"",SUMIF(B,B5,N),"") Since I got this from somewhere else, I don’t really understand all part of it, thus don’t know what to change.

The problem is that you use A5 and B5 in your formula and bring that to the bottom of your sheet. On line 8 it totals the amount of the person of B8, but that person is on line 6 of your column Q. So I don’t think you can make this work with this formula. I think you need to use a pivot table to summarize:

Since I’m using the Number app, I can’t do pivot tables. At least that’s what I’ve read.

I’ve found a workaround where I kept the original list in column O and P and the list of names without spaces in the next column.

Then I changed the problem column to do a lookup on column O to select the right amount to complete the data. Works fine and I can hide O and P. It just annoys me that I need the extra columns so I’m still looking for a cleaner solution.

@dabigkahuna: Robert, I am late to this conversation, and excuse me if I wear the hat of a teacher.

I approach this type of problem a bit different. Numbers is great for having multiple Tables in a single Sheet. I would have a Table for the raw data like you have and assign a consecutive ID number to each Donor. [Note: You have already solved the unique ID as it is in Column A.] Then in another Table I would have the list of donors and summary information. What is nice about Numbers is that you can put these two Tables side-by-side and see them both at the same time. Excel can do this too, but everything is a separate worksheet on separate tabs. You can skip the separate Table if you like, but it gives you the feature of having separate footers and header regions and be named. You will note that I have footers and headers in my example.

I have attached a simplified example.

The formulas are as follows:

Feel free to ask questions, since I have not explained why I did these formulas as I did. For instance the formula in A2 of Donors Table uses Max function, so that the formula is the same for all rows including the first one. You could have made A2 = 1 and then A3 = A2+1, but I am a bit eccentric when it comes to spreadsheet design. Note also the $ sign in formulas means that this value does not change when the formula is copied. It is anchored in a sense. Also sense I used header and footers, I can select a column and not worry about it processing the header nor footer.

I hope this helps.

Looks very interesting. I’ll be trying to adjust it to work on one spreadsheet though. There are pluses and minuses to both approaches but, for now at least, I want it on one. Just can’t look at it in detail for a few hours.

Thanks. Hope it works out!

Just got back and starting to look at this. I see a difference in your column A (mine starts at A4). My formula is:

IF(COUNTIF(B$4:B5,B5)=1,MAX(A$4:A4)+1,"")

Your formula (started in A2) is, assuming I don’t make a typo:

If(COUNTIF(B$1:B1,B2)=0,MAX(0,A$1:A1)+1,"")

I tried your formula, after adjusting the row references to match my setup (adding 3 to each row reference) and it seems to give the same results.

You talked about using “MAX”, but my formula had that.

Anyway, for starters, is there anything better about your formula than the one I used (note that I just used a formula I found someplace else, even though I don’t understand every aspect of it.

Meanwhile, I’ll start studying the rest of this.

UPDATE: I do see one advantage of the formula I used. When your’s covers rows that have no data, it gives them a number. Normally this would only happen at the end of the data with unused rows below, though it could do it if for some reason I skipped a row.

The formula I used doesn’t do that.

Next step. For now, using a separate table for Donors, I copied your Column A formula. I changed the references for A2 to be A5 since my data starts on row 5. For your reference, this was my result assuming :i didn’t make a typo:

IF($A5<=MAX(income::A),LOOKUP($A5,INCOME::A,income::Name),””)

But I get an error. So the following is copying the formula from the spreadsheet, instead of just the text version of it shown above:

IF($A5≤MAX(Income::A),LOOKUP($A5,Income::A,Income::B),"")

Note that it changed the reference to “Name” to be “B” - which is the column the names are in. Don’t know if that is the problem or something else is wrong. But, obviously, if I can’t get the names copied over, I’m stuck anyway!

Meanwhile, to check the next formula, I guess I’ll temporarily put the names in manually so I can see if the next part works.

Well, that didn’t work either. So I guess I’ll recreate your examples, ignoring my original for now, just to make sure that works before trying to see how to adjust it to my structure.

UPDATE: I created two new tables for testing, just like yours, though instead of your names, I named them “Test1” and “Test2” and changed the references in the formulas.

In Test2, I was able to get the names to show up in column B (though still have the issue of unused rows on Test1 showing up as “0” in that column).

Can’t get column C to show the money at all. Just an error. Here is a copy of what is in C2:

IF(Name’MAX(A1,0)+1’<>””,SUMIF(Test1::Name,Name’MAX(A1,0)+1’,Test1::Tip in USD),””)

Another issue, probably related, is that when I click on that cell to see the formula and edit it, it doesn’t show up as a normal formula. You know, how numbers takes the plain text and makes things look fancy. Even though the formula thing is at the beginning, everything just shows up as plain text!

Maybe I have a typo I keep overlooking. But if you could copy and paste your formula in a post rather than me trying to retype from the image you sent, I could copy it directly so at least I’d know there was no typo.

Sorry for my slow response.

Your formula is less complex. I have gotten the habit of putting 0 in the Max function in case none of the cells in the Max range has a valid numeric value. Mathematically, this is more correct and is a necessary thing to do in Excel. Evidently, if there is no valid numeric values in the range, then Numbers returns 0. This is a very minor point and I love that your formula eliminates incrementing the index on blank rows. Very good.

Continuing on with your second message.

Numbers names the columns when your heading is simple and more conventional like mine to improve readability. Since your heading is multiple rows, Numbers does not always have a simple name for these columns. I am glad you figured out Income::B works. When I wrote my formula, I simply clicked on the B column heading and Numbers does the rest. I should warn you about the Lookup function, if it does not match anything it returns the results that closest matched the search. Since we have a correct by construction setup, we do not have to code around this. A more robust solution is to use the Match(search-for, search-range, matching-method) with matching-method set to exact, and putting this inside of Index(range, row-index). So we could have done your lookup with INDEX(Income::B,MATCH(A5,Income::A,0)). This will give an error if it doesn’t exactly match.

Actually, It was MY typo. I don’t know what happened when I Cut and Pasted this formula, but it should be

IF(B2≠"",SUMIF(Income::Name,B2,Income::Tip in USD),"")

The IF is used to only call the SUMIF, if there is a person to summarize in the B column. The SUMIF I think you have used before. In Numbers when you are typing in formula, you can click on the function popup and select Show in Function Browser to read about the arguments and see some examples.

You are doing great, but I again apologize for the Cut and Paste error. I am a poor proofreader.