Advice Needed: Performing complicated nested if calculation
Hi there, I am trying to find a way to automatically calculate a sum, based on a number of variables. I can't figure out whether an extremely long series of nested Ifs in a Formula, or a VBA function, or something else might be the most "straight forward way" to do it. Regardless of the method, I'm having a lot of difficulty in figuring out how to write it. The sum, if applicable, at its most base level is: ((Rate A - Rate B)) * Number of days in the month) Relevant factors: Not all rows need this sum calculated. Of those that do, they have independent values for Rate B. Rate A can be either of a set of values, depending on whether another cell is <40% or not Rate A and/or Rate B can be changed in a month by a third party If Rate A is changed, it's a fixed change within the month If Rate B is changed, the date of the change may differ from row to row. Whether the row needs the sum calculated, and which of the sets of values Rate A should be using are fairly simple to write up. The problem I'm having is with the third, as the potential outcomes are: No change to either Rate A or Rate B, the sum is ((Rate A - Rate B) * Number of days in the month) Rate A changed, Rate B didn't the sum is (((Initial Rate A - Rate B) * Number of days between beginning of month and date of change) + ((New Rate A - Rate B) * Number of days between date of change and end of month)) Rate B changed, Rate A didn't the sum is (((Rate A - Initial Rate B) * Number of days between beginning of month and date of change) + ((Rate A - New Rate B) * Number of days between date of change and end of month)) Both Rate A and Rate B changed, in which case my life is very very hard :( Rate A changed before Rate B did (((Initial Rate A - Initial Rate B) * Number of days between start of month and date of Rate A change) + ((New Rate A - Initial Rate B) * Number of days between change of Rate A and change of Rate B) + ((New Rate A - New Rate B) * Number of days between change of Rate B and end of the month)) Rate B changed before Rate A did (((Initial Rate A - Initial Rate B) * Number of days between start of month and date of Rate B change) +((Initial Rate A - New Rate B) * Number of days between date of Rate B change and date of Rate A change) +((New Rate A - New Rate B) * Number of days between Rate A change and end of the month)) Can anyone point me at which of the available methods available in Excel would be the best to use? I got started with both nested Ifs and VBA. Nested Ifs are difficult to troubleshoot, with so much in them. VBA I realised I was going to end up with a certain amount of nested Ifs anyway, which prompted this question... should I even be using VBA? Help!
Transfer Quicken Data into Weekly Report
1/2/2024 1893 Res PAYPAL TRANSFER 240206 Tithe 650.15 1/3/2024 1893 Res PAYPAL TRANSFER 240213 Tithe 96.62 1/4/2024 3022 Youth DEPOSIT MADE IN A BRANCH/STORE Donation 25 1/4/2024 2104 Main DEPOSIT MADE IN A BRANCH/STORE Tithe 0 1/8/2024 2104 Main DEPOSIT MADE IN A BRANCH/STORE Tithe 690 After converting CSV into Excel format and filtering the above, the data represents what must be placed into the appropriate account (column), totaled, and then totaled into weekly segments. We need a formula for a year's worth of data to accomplish this task. There are two other columns not included: 9542 BkSt, and Bldg Fund Week Ending 2104 Main 1893 Res 3022 Youth Total 1/1/24 - 1/6/24 1/7/24 - 1/13/24 1/14/24 - 1/20/24 1/21/24 - 1/27/24 1/28/24 - 2/3/24 2/4/24 - 2/10/24 2/11/24 - 2/17/24 2/18/24 - 2/24/24 2/25/24 - 3/2/24 3/3/24 - 3/9/24 3/10/24 - 3/16/24 3/17/24 - 3/23/24 3/24/24 - 3/30/24 3/31/24 - 4/6/24 4/7/24 - 4/13/24 4/14/24 - 4/20/24 4/21/24 - 4/27/24 4/28/24 - 5/4/24 5/5/24 - 5/11/24
Unable to create dependency drop down List
Hello All, After a long search I still could not figure out how to create this dependency drop down list in excel. What should I input in Data validation:List? May I have your help on it: My target is to create drop down list in below Main column. It should be dynamic. Expected result:For apple, it should be able to select Green or Red. Orange can select Orange I will add more rows on sheet Main and sheet second if there is more combination. Seems this is simple logic but already spent hours on it :( In sheet: Main Composition column Drop down list in this column Apple Apple Orange In sheet: Second Fruit Drop down list Apple Green Apple Red Orange Orange Pineapple Yellow Thank you.
Gantt Chart without weekends
I have a Gantt chart in Excel to help us with project management and timing. I found an example a few weeks ago and have modified it to get about 95% of the way done. The issue is weekends. The chart, and the calculations, are using a full 7-day week and not a 5-day work week. Attached is a screen capture. L5 contains the formula that starts the chart. =Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1) Project_Start is the range T1:AC1, although I'm using it as the end/launch date of the project. My task calculations work back from that date. Display_Week is the range T1:AC2, again I'm working backwards from the launch date so that's a negative number here. As outlined in the red box, the tasks and chart are including weekends. How do I fix that so it calculates dates based on work week vs calendar week and also doesn't display weekends on the chart itself? Thanks!
Formula Needed
Using Alexis as an Example, I need Excel to search Column A for all the versions and then avg the data for the same Column found in B because sometimes there are multiple for the same Column. As in Column D. Then I would like the avg'd information to be put into H-J respectively for each name in Column G. Can this be done? I assume its going to involve MAP, Index? Where I am stuck is Column A includes the Column G but not exact match and the AVG data found in the Columns B-D and puts it in its corresponding column H-J.
Auto fill Department column
Good afternoon Microsoft Community, This is my first post ever with this site. I have the following project that I need help with. I need to auto fill the Department in column A in the Raw Data worksheet, with the DEPARTMENT name on worksheet Supervisor Drop-Down list. The Raw Data worksheet has Column F with the names of the current employees. I want a function/formula that will check for the names in the Supervisor's Drop-Down list and automatically add that department to Column A on the Raw Data worksheet. I have explore many different videos on the web and have not been able to find a solution. please advise on the best approach to handle this task.
Complex look up with output
Hi, I'm looking for help with a complex lookup problem. I have tried a number of options but haven't been able to achieve the desired result. If it were possible to use Conditional Formatting to highlight unique values incolumns from 2 different Excel sheets that updates when filters have been applied to each then this would be a simple solution, but unfortunately Conditional Formatting does not update/adapt to filtered data (that I can see), so I'm trying to achieve something more complex and dynamic with Ifs, AND, Lookups etc... Basically, a simplified version of the problem (which I have created a sample Excel sheet for this but there doesn't appear to be anywhere to upload a document) is: Lets say I have a list of training courses in a table with column headers for different Grade and within each Grade column there is L1,L2,L3... representing different levels. On another sheet I have a report of students who have competed various courses. I want to search the table by Grade an level for respective courses and search the students report to see if they have those courses complete. Any courses not listed in the student report, for that student, needs to then be displayed in a list so that it the courses can be assigned. The idea would be that multiple cross checks could be done for each student who is in a different Grade/level to see what they are missing. I created a 3rd tab with dropdown menus for the 3 variables, where the output list of courses should be displayed. Is that possible? Many thanks in advance for any help with this.Excel functions for column compares
I'm new to Excel and want to find a function that compares one column to another and if the value goes up sends me an indication (trend up) and if the value goes down (trend down). For example Total Count Most Recent Today Count 15,054.29 10/30/24 18607.14 4.57 10/10/24 4.57 6,692.13 10/30/24 6889.51 3,504.65 10/30/24 4191.37 If Total Count is larger than Today Count - return trend down indicator If Total Count is less than Today Count - return trend up indicator If Counts are equal - no trend indicator Is there a function that can do this on Excel?
