Posts by EnginerdUNH

EnginerdUNH · May 30th 2018

Hi,

i am am looking for a way, whether formatting or coding, to display an integer and decimal pair as a fraction. For example, a whole number like 2 would display as that number over 1, i.e. 2/1. A number like 2.5 would display as 5/2 and not 2 1/2. Not sure if there is a simple way to make this work or not.

Thanks,

Morgan

EnginerdUNH · Jan 4th 2018

Ahhh yes!! Thank you so much!!

EnginerdUNH · Jan 4th 2018

Quote from Luke M

We can solve half the problem by removing the self-referencing sheet names from the formulas, and just have the cell address. To make your formulas more robust on looking things up from the refrence sheet, you could use the power of lookup formulas to let XL figure out which column and row it needs to be looking at.

In the attached, I first changes all your dates to be real dates, rather than just strings. Then, I setup a lookup range on your Monthly EV sheet. Last, I changed the main formula in col F to use lookup formulas to figure out which column/row to extract data from. All of the year sheets have the exact same formulas, no need to change anything when you copy the sheet. There's even a formula on the worksheet that pulls in the sheet name, so it knows what year you're trying to find.

Hi Luke,

First off let me thank you for helping me out with this. I am having a few issues with the solution that you provided, however. The solution provided completely disregards in column F any data entered in columns C and D on the year tabs. Also, column B should only be compared to the 4 ER exclusive columns (Columns C through F on my example spreadsheet) on the Monthly PV sheet. Columns C and D are applicable to comparison with the remaining columns (Columns G through I on my example spreadsheet) on the Monthly PV sheet. How does the formula in column F need to be modified to account for this?

EnginerdUNH · Jan 4th 2018

Hopefully my explanation of what I am looking to do is pretty understandable but I will do my best to answer an questions you have. I have a spreadsheet where each tab represents a different year with one tab being a reference information tab for use in formulas/VBA on each year tab. Right now, when I create a new year tab, I have to copy the information from a previous sheet and modify the formula to the reference the correct year sheet and the to call out the correct row on the reference information sheet. What I am looking to do is see if there is a way to modify the formula I am using, or create VBA code, to change dynamically with the creation of new year tabs. Let me try to break down what I envision the formula/VBA being able to do:
1. use VLOOKUP to search the reference tab for the correct row and column corresponding to data that I provide
2. use a formula to determine which rows on the reference tab match the year (sheet name) that I am working with
3. determine the row on the reference tab that corresponds to the month I am working with on that same sheet
4. search that row for the column that matches the criteria I provide
5. sum up all determined values
6. multiply value by .76
[ATTACH]n1197583[/ATTACH]
I have provided a test spreadsheet that I have been working with for your reference of how I am doing it so far which doesn't involve the method I described in steps 1 - 6 as of yet. The column which has the formula in question is column F on sheets 2018, 2019, 2020 and 2021 (for right now). For your reference, what I am doing right now is when I create a new sheet, I copy and paste the previous year's sheet and I have to then change the sheet name in all references in the formula as well as determine the correct corresponding row on the reference numbers sheet (2 - 13 for 2018, 14 - 25 for 2019, 26 - 37 for 2020, 38 - 49 for 2021, etc.) I have done some research already and found that I can potentially use the INDIRECT formula within VLOOKUP to reference the sheet name but i'm not 100% confident that I know how to do it. Please feel free to modify/tinker with the attached spreadsheet and provide any solution you think will work for me or ask questions on something that doesn't make sense.

EnginerdUNH · Jul 2nd 2017

Re: Count number of non-blank cells in rows and columns

Quote from KjBox;795089

Still a bit confusing.

Can you attach your workbook showing the desired results (manually enter those in the cells where you want them to be displayed) and explain the logic behind each result.
[sw]*[/sw]

forum.ozgrid.com/index.php?attachment/72963/

I may have come up with a solution to my problem but I'll run it by you first. Since every person will have data from their first month onward, even if it's zero, can I just used the Count function to count the number of filled cells in a particular row and then subtract it from the number of filled cells in the previous row to get the number of new cells that are filled?

As for the distinction between the totals for returning and new people, I want a way for the spreadsheet to tell me what the total is by adding up just the values corresponding to returning people in a month and a separate column telling me the total amount pertaining to the new people. 9/10 the value for the month corresponding to each individual person will be 100 and therefore one would assume that I can just go ahead and say ok we have 2 new people this month so the sum is 200. However, I need this to be dynamic in the sense that if I have one person whose total is 100 and another who is 150, the spreadsheet isn't just going to assume it's as simple as 100*2 but would actually be 100+150.

I have manually entered in the information as it should be calculated into the example spreadsheet.

Does all of this make sense?

EnginerdUNH · Jul 2nd 2017

Hi,

Lets say I have the following table:
[TABLE="class: grid, width: 500"]

[tr]

[td]

Month

[/td]

[td]

Person 1

[/td]

[td]

Person 2

[/td]

[td]

Person 3

[/td]

[td]

Person 4

[/td]

[td]

Person 5

[/td]

[td]

Person 6

[/td]

[td]

Person 7

[/td]

[td]

Person 8

[/td]

[td]

Person 9

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

150

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td]

100

[/td]

[/tr]

[/TABLE]
I am trying to create a simple (if possible) formula that will add up the total for every month corresponding to each person if they meet the criteria that there is 3 months or less (think of them as cells) worth of data in those columns, including the current cell. Let me give some examples:

for Month 1, Persons 1 and 2 have data while the rest do not. They also meet the criteria of not having previous monthly data. Therefore, for Month 1, the total I want to sum up comes out to 200.

For Month 2, Persons 1 through 4 now have data but let's check and compare to the previous months. Persons 1 and 2 also have data in the previous month but the number of cells so far in columns 1 and 2 is only 2 so therefore they are still included in the summed total. Persons 3 and 4 fit the category described above for Month 1. So, the total for this month is 350

For Month 3, Persons 1 through 6 now have data but we have to check and compare to the previous months. For Persons 1 and 2, this is their 3rd month of data but that meets the criteria I set above. For Persons 3 and 4, they fit the category described for Month 2 seeing as they only have 2 months worth of data. Persons 5 and 6 meet the category for Month 1 since they only have 1 month worth of data. So the total for this month is 550.

For Month 4, There are now 9 people that have data but lets check and see which ones meet the criteria I stated above. Persons 1 and 2 now have data for 4 months (3 previous months and the current one) so they fail the criteria above and are therefor excluded. Persons 3 and 4 meet the category of Month 3 and are included. Persons 5 and 6 meet the category of Month 2 and are included. Person's 7, 8 and 9 meet the category of Month 1 and are included. So the total for this month is also 550 and not 700.

I understand that the criteria doesn't ever get broken until Month 4 so if necessary, a formula that works for Month 4 onward is fine but I would prefer a formula that would work for every month meaning that it would dynamically change to include more previous months as the number of the current month increases.

EnginerdUNH · Jul 2nd 2017

Re: Count number of non-blank cells in rows and columns

Quote from KjBox;795066

Your explanation is confusing.

For Month 1 the 2 cells that are not empty are in a column that has 4 non-blank cells, so, why is the required count not 0?

For Month 2, with the 4 cells that are not empty 2 are in a column that has 4 non-blank cells, so, why is the required count not 2?

For Month 3, with the 6 cells that are not empty 2 are in a column that has 4 non-blank cells, so, why is the required count not 4?

The condition you stated would appear to apply only to month 4 in your stated desired results.

Display More

Those are all good questions and looking back I realized I made a few mistakes in my wording. The required count should be 2 every month according to the sample data I provided, however, I do not just want to type in 2 everywhere where I need this data because let's say I change one month and now the required count is 3. I want it to dynamically change. To explain this and simultaneously answer your questions, lets see if this makes sense. For Month 1, you're right about the two non-blank cells being in columns that contain 4 non-blank cells. But, for Month 1, I'm only concerned with Month 1 data (i.e. look across the row, see 2 non blank cells, check those columns and see this is the first non-blank cell in each, answer is required count of 2). For Month 2, I'm only concerned with Month 1 and 2 for data. Look across the row, see 4 non-blank cells, compare to the previous month and see that two of the cells for Month 2 also have data for Month 1, the required count is again 2. Can you see the pattern now? Hopefully this makes sense.

EnginerdUNH · Jul 1st 2017

Hi,

I am trying to come up with a formula that will count the number of non blank cells in each row and compare it to the number of non-blank cells in each column and decide if it should add them up. Let me give an example of a spreadsheet I am using and then try to explain it further.[TABLE="class: grid, width: 500"]

[tr]

[td]

Month

[/td]

[td]

Person 1

[/td]

[td]

Person 2

[/td]

[td]

Person 3

[/td]

[td]

Person 4

[/td]

[td]

Person 5

[/td]

[td]

Person 6

[/td]

[td]

Person 7

[/td]

[td]

Person 8

[/td]

[td]

Person 9

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

150

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td]

100

[/td]

[/tr]

[/TABLE]
Let's say I have the table above. I want to add up the number of blank cells in each row but I only want to include the cells in the count if the number on non-blank cells in a column is less than or equal to 3. For example, for the first month, the number of non blank cells to count is 2. For the second month, the number of non blank cells is 4. For the third month, the number of non-blank cells is 6. However, for the fourth month, the number of non-blank cells is seven because the columns for person 1 and 2 have 4 rows worth of data.

In addition, I also want to have a way to use this same criteria to sum up the values in the corresponding cells. For example, for the first month, the total is 100. For the second month, the total would be 350. For the third month, the total would be 550. But for month 4, the total would also be 550 because the first 2 columns are excluded.

EnginerdUNH · Oct 10th 2015

Hi,

I am trying to create a formula that will calculate a value based on a particular condition that I give it. Here is what I basically want the formula to say: "sum up all the values in this array given the following condition: if the value in the box is less than 20, add that value to the total. If the value in the box is greater than 20, add 20 to the total." I would like for there to be a way if possible for me to be able to have the formula work so that if I add more values to the total I want it to give me, it will include those values in the calculation. Let me try and show an example table or two of that I mean.

[TABLE="width: 500"]

[tr]

[td][/td]

[td]

14.75

[/td]

[/tr]

[tr]

[td][/td]

[td]

24

[/td]

[/tr]

[tr]

[td][/td]

[td]

6.5

[/td]

[/tr]

[tr]

[td][/td]

[td]

20

[/td]

[/tr]

[tr]

[td]

Total

[/td]

[td]

61.25

[/td]

[/tr]

[/TABLE]
So as you can see, the formula for the total would add up the 14.75, 6.5 and 20 just as the are and would only add 20 from the 24 value. Now if I were to add a few more rows to the table above my total row, I want the total to change so that it is also including the new values I'm adding. For example,
[TABLE="width: 500"]

[tr]

[td][/td]

[td]

14.75

[/td]

[/tr]

[tr]

[td][/td]

[td]

24

[/td]

[/tr]

[tr]

[td][/td]

[td]

6.5

[/td]

[/tr]

[tr]

[td][/td]

[td]

20

[/td]

[/tr]

[tr]

[td][/td]

[td]

28

[/td]

[/tr]

[tr]

[td][/td]

[td]

15.8

[/td]

[/tr]

[tr]

[td][/td]

[td]

13

[/td]

[/tr]

[tr]

[td]

Total

[/td]

[td]

110.05

[/td]

[/tr]

[/TABLE]
If there is a way that I can create a formula using IF THEN or conditional statements, that would be ideal because I would be able to have the formula add the values and not have to add them myself every time I update the table.

EnginerdUNH · Sep 8th 2015

Hi,

I'll try and explain this as best I can and hopefully this will make sense to anybody reading it. I use excel to enter a lot of data and it can get very annoying sometimes when I have to create a new workbook because my spreadsheet either becomes too large or I have to change something and I have no way of knowing how to make the spreadsheet look best so I can still continue my work. I am looking to see if there is a way that I can do basically a double freeze panes so that my headers will switch to the header I want when I scroll down to it. Here is an example of what I mean:
[TABLE="width: 500"]

[tr]

[TD="align: center"]Heading 1
[/TD]
[TD="align: center"]Heading 2
[/TD]
[TD="align: center"]Heading 3
[/TD]
[TD="align: center"]Heading 4
[/TD]
[TD="align: center"]Heading 5
[/TD]

[/tr]

[tr]