Problem creating an Excel formula
I am contacting you because I am having trouble creating a formula to count names in an Excel spreadsheet. I have created a schedule in Excel in which there is usually one name per cell. The calculation of these simple cells is fine. However, in cells where there are two names written ‘NAME F.1 / NAME F.2’, only the first name is counted. I do not know how to make the second name count as well. If necessary, I can send you the Excel file. Thank you for your help, as I do not know how else to solve this problem.
Excel Formula Help!!
Hi, I'm looking to write a formula that can help my class with some basic budgeting skills. They basic layout is each column is it's own category with an allotted amount for each: ex column A is for groceries with a total of $90 to be spent in that pay period. Each column has a sum cell (B31) so students can see how much they have spent and right beneath it in the same column, there is a cell that lets them know how much of the allotted amount for that category they have left. I was wondering if there was a way to modify it so that in the cell (ex: B 32) that currently displays the remaining balance for that category if gone over budget (as in the number is now in the negatives) could be stopped at zero and the negative balance could be subtracted equally from other categories? To visualize what I've done so far: The formulas for row 13 are =sum(ColumnLetter2:CL12) Row 14 is where it differs by each cell; A: =minus(90,A13) B: =minus(100,B13) C:=minus(120,C13) D:=minus(50,D13) E:=minus(40,E13) F:=minus(60,F13) G:=minus(90,G13) H:=minus(75,H13) I:=minus(50,I13) The main question comes once students start filling in the spreadsheet. With the hypothetical fill of : How can I create a formula in row 14 that continues to show remaining balance, but only when a column reaches negative (like the beauty) takes the negative value (15 dollars) and equally subtracts (1.875) it from the remaining columns that can have that amount subtracted without going negative themselves? I wouldn't want any of the overspent value of beauty to be taken from Misc. as there is only one dollar left and that is less than the evenly split amount (1.875). Is there a way to write such a formula into the formula for row 14 or would it need to occur in a different row? I'd love for my students to be able to see how to manage the occasional sometimes necessary overspending in one category to see how to adjust spending in others (hopefully it wouldn't be for beauty lol). I've thought about using If formulas, but am struggling on how to fit in all the criteria. I would appreciate any help in creating a formula!! Thank you so much!!!
Summing rows values while a specific column answers a specific condition.
Its much smaller than the sheet I work with but for this example I want to scan column 1 representative and if the value in a cell form A2:A6="D" I want to sum its rows. in this case I expect the answer 3+1+8+1=13. Tried the sumif but it works for 1 column at a time and not scanning multiplied arrays. Tried sumproduct and still couldnt get the cell to show the total amount. since in my actual worksheet there are 37X57 array, going manually and put formula in each specific row is less ideal soo I am looking for a specific command to solve it and calculate along the data that changes on the sheet.
How to distribute the value of one cell evenly in a sequence for the value of another cell?
Hi there, I'm trying to evenly distribute numbers (not a set value) to help with crocheting - taking the math out for testers/creators right now I have the blue row working as a sequence, however I can't work out how to divide it equally So for the example above; there are 20 rows, there needs to be 8 increases (1 every 2.5 rows). How do I get that information to show in the column next to my sequence (Rows)? If it's even possible - haha. I would like it to automatically work out like the below (manually entered) Thank you
Football Pool Sheet Using Logical Formula
I run a local football pool and I developed a great spreadsheet, but I want to add another wrinkle to it I type in a W or L based on the result of the score to each teams spread I copy and paste all the scores based on rotation so its easier to sort I want to have the formula automatically insert a "W" or "L" based on the difference in score and spread after i paste all the scores If you think I need to add or do something else to make it easier, please let me know I WOULD LOVE TO KNOW HOW TO LINK LIVE TIME SCORES WITH A WEBSITE, BUT ONE STEP AT A TIME LOL THANK YOU!!!!!Building relationships in data model to leverage power pivot - circumventing unique key ID issue
When building relationships between two data tables in the data model of excel, you require a unique ID / key to relate the two datasets. This makes sense but I find it has an issue: It requires you to reduce the entirety of your dataset down to unique ID's in one of your tables so that it can do the 'one to many' relationship build. If you don't have unique ID's / key, the relationship between two tables often doesn't work. Yes you can use an intermediary table with unique ID's to link the two complete tables, but when using that intermediary table I find you are limited in what additional data rows you can bring into the combined power pivot table to provide context to the relationship. Once you start bringing in contextual rows from the separate tables, there ends up being a lot of repeated values for any numerical figure from the other table, which you are trying to aggregate. An example below of the power pivot table result (data from table 1 in blue, from table 2 in green) Product ID (Unique Key) Service Line Sales Amount COGS 169AKY T Shirts $1,000 $7,500 169AKY Sweaters $2,000 $7,500 169AKY Socks $3,000 $7,500 169AKY Turtle necks $4,000 $7,500 169AKY Pants $5,000 $7,500 COGS from table 2 also has the data broken out by Service Line (T-shirts, Sweaters, etc.), but will only show an aggregate COGS value for all service lines for each unique product ID, instead of breaking it out by Service Line. This is because the Service Line column is being inserted into the power pivot table from table 1, it breaks out sales by Service Line. If you insert the Service Line data column into the power pivot from table 2, COGS would be broken out by Service Line, and only an aggregate for sales would be produced. Ideally the power pivot would display sales AND COGS broken out by Service Line (and Product ID). Is there a best practice for building relationships (unique ID's) between data tables: Without reducing the richness / variety of columns and data in your original tables (when creating the unique ID)? Maybe 'group by' function in power query to obtain unique ID's before building the relationship? With retaining the ability to slice and dice the data by different characteristics (assuming they are present in both source tables) and have that detail presented in the power pivot. Thanks for the help! The humble Apprenticeformula help
I need some help figuring out this spill error. I'm trying to take the information from column 2, column 15, and column 16 when column 18 does not have an x and automatically populate that information into employees staying late what rf gun they have and what temp probe they have. it is showing me a spill error and i can't figure how to fix it. I'm still new and learning. i also provided the formula that i currently have in that cell.Hide Sheet after missusing shortcut Alt+H+W
I was working on an inventory and frequently using the shortcut mentioned above. Unfortunately, during one of those times, I must have misused it, and my sheet became hidden. Since then, I’ve tried multiple methods to unhide it, but none have worked. I would really appreciate your assistance in resolving this issue.
