Dell / Inspiron 17r

I am using the array formula {=SUM(IF(ISNUMBER(FIND("B",P9:AT9)), VALUE(RIGHT(P9:AT9,LEN(P9:AT9)-2)),0))} which was graciously provided by DerbyDad03. It is used to record "B" values, e.g. B:1.2, which are in individual cells in an Excel spreadsheet. I also have array formulas for "T" values and "W" values. I tried to rewrite the arrays so that a combination of two variables ("B" and "T", "B" and "W" and "T" and "W") in the same cell would be recorded but each time I try something it is rejected. Thank you,

Brian W

✔ Best Answer

Sometimes making the data entry just a little more difficult is actually the easiest solution in the long run. The more complex we make a formula to work with a "simplified" data entry process, the better the chance that we will run into problems in the future.

message edited by DerbyDad03

Brian, Once again, you have not provided enough information for us to be able to help you.

We have no way of knowing what you mean by "a combination of two variables".

Based on the original description of the data e.g. "B:1.2", I could come with so many variations of "a combination of two variables" that your head would spin.

WB:1.2

BW:1.2

W:1.2 B:1.2

W:1.2B1.2

W:1.2, B:1.2

W:1.2 B:3.1

B:3.1 W:1.2I could go on for hours.

Tell us

what your "a combination of two variables" looks like and maybe we can help. If there is more than one way that "a combination of two variables" could appear, then you need to tell usexactlyevery waythat "a combination of two variables" could appear so that we can avoid wasting time trying to figure out what your spreadsheet looks like.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

DerbyDad03, Sorry for the confusion but it’s difficult for me to explain something that I do not understand. Currently each cell is either blank or contain a number from 0.1 to 20.0 with a prefix of either B, T, or W. What I was trying to do was to have two values with prefixes of B and T, B and W, or T and W in one cell. I tried modifying the array formula that you previously had given to me but I was unsuccessful.

Thanks for your assistance.

Brian W

re: " Sorry for the confusion but it’s difficult for me to explain something that I do not understand"I'm not asking you to explain something that you do not understand. I am asking you to show us some example data so that we know what we are working with.

Let's go back a few months to your other thread...

After a few tries, I believe that we eventually got to the point where we all agreed that your data looked like this:

A B C D E F G 3 T:10 B:4.5 T:6.2 T:6.2 B:4.5 B:0 T:6.2Once we got to that point, and you told us what the results should be, we all had the same set of requirements to use as we looked for a solution.

What I'm asking for now is a similar example of what your data looks like when you say "a combination of two variables". Just an example of a few cells that have

"two values with prefixes of B and T, B and W, or T and W in one cell". What the final answer should be based on that example would also be extremely helpful so that we are not guessing if we are getting the right answer during our tests.In this thread you said:

"I tried to rewrite the arrays so that a combination of two variables ("B" and "T", "B" and "W" and "T" and "W") in the same cell would be recorded but each time I try something it is rejected."I have to assume that you tried to apply the re-written formulas against a set of data. All I am asking for is that you show us what that data looks like and what the expected result should be. If you can't tell us what your data looks like, we can't help you.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

">">DerbyDad03, This is what I should have done in my first post: (from one of your previous posts with current modifications.)

I have a row of cells (n = 31) that start with either B:,T: or W:. Each letter is then followed by a value that ranges from 0.1 to 20.0. I have 2 requirements:

1 - Count the number of cells that begin with each letter.

2 - SUM the values that follow each letter.I have the following data set: (It does not matter which prefix comes first in a cell.) [I can not align values with rows and columns.]

A B C D E F G

3 T:10.0 B:4.5/T:1.2 T:6.2 W:6.2 B:4.5/W:1.2 B:2.3 T:6.2/W: 1.7

The results should be:[I can not align values with rows and columns.]

A (see #1 above) B (see #2 above)

1 4 23.6 (for T)

2 3 11.3 (for B)

3 3 9.1 (for W)Hopefully, this clarification will help. The array formula that you previously sent to me works fine when there is only value in a cell. (For "B":) {=SUM(IF(ISNUMBER(FIND("B", P9:AT9)), VALUE(RIGHT(P9:AT9, LEN(P9:AT9)-2)),0))}

Thank you.

Brian W

message edited by Brian W

re: " [I can not align values with rows and columns.]"The link at the bottom of every one one my posts explains how to post example data in this forum. Many others have found it very useful.

In addition, Mike M has written a more extensive How-to with detailed instructions.

https://www.computing.net/howtos/sh...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

DerbyDad03, Thank you for that information; do you want me to repost #4 with correct spacing of rows and columns?

Thanks again,

Brian W

You can edit #4 or repost as new. I don't want to guess as to how your data is laid out.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

DerbyDad03, Here is the data from #4:

A. B. C. D. E. F G

3. T:10.0. B:4.5/T:1.2. T:6.2. W:6.2. B:4.5/W:1.2. B:2.3. T:6.2/W:1.7

message edited by Brian W

I think you should try that again.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

DerbyDad03, I couldn't get the alignment correct so I used File Convoy:

http://www.fileconvoy.com/dfl.php?i...

Thank you.

Brian W

Is B1 really supposed to contain this? B:4.5/T:1/2

The slash between 1 & 2 is inconsistent with the rest of your data and I don't want to go off chasing a solution that is based on a typographical error.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Brian, If your having problems formatting your data on the forum, read this new HOW-TO

hopefully will make your posting easier.https://www.computing.net/howtos/sh...

MIKE

Brian, Both of the cells that contain a B and another value, have the "B" as the first character.

B:4.5/T:1.2

B:4.5/W:1.2

Will there ever be a case where the B will be after the slash? e.g.

W:1.2/B:4.5

T:6.2/B:2.3

Basically, you need to ensure that you include all possible combinations so that we can test our suggestions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

DerbyDad03, No, there will not be a case where B will be after the slash.

Thank you.

Brian W

Brian I have not been able to figure out a formula for this.

Having separate figures in the same cell means you have to some how split the cell and account for two separate figures within one formula.

The logic for the entire formula would go something roughly like:

First determine if a cell has a Slash character or not, then

If No Slash we can use DD03's formula. (Nice, easy, simple)

If Slash, then what character comes first, B, T or W ?

If B is first then use the LEFT, LEN & FIND functions to get number,

Now what character comes next T or W

If T is second then use MID, LEN & FIND to first determine location of the slash,

then get the number using MID or RIGHT & LEN functions,If NOT T then must be W, then use MID, LEN & FIND to get number,

(Because B is always first it makes this section easier.)

If T is first then use the MID, LEN & FIND functions to get number,

W is second then use MID LEN & FIND to get number,

If W is first then use MID LEN & FIND to get number,

T is second then use MID LEN & FIND to get number,I'm not even sure if the formula could allocate the right numbers to the right letters.

I think that a VBA solution would probably be possible,

but I would suggest, a simpler way is to use thefunction if you can and simply insert a temporary column to split apart the cells with a slash and use DerbyDad03's original formula.Text To ColumnsMIKE

message edited by mmcconaghy

mmcconaghy, Thank you for trying to resolve this issue. I thought of a possible way that on its surface would solve this problem. Currently, the column headers are numbers from 1 to 31 to represent the dates for each month. What if I doubled up the numbers such as 1, 1, 2, 2, 3, 3... 31, 31. In the first “1” column, I would put in only “B” values and it the second “1” column, I would only enter “T” or “W” values. Do you think that this would work?

Thanks again,

Brian W

Any way you can split the cells should work. (Famous last words.) How is the data entered?

Do you import the data from some where? If you do, what form is it in, .CVS?

If CVS, you can pre-process the data before importing.If it's entered by hand, any way you can have the person/s who do the data entry split the cells?

MIKE

mmcconaghy, I enter the data by hand. It’s an exercise tracker for me; B = exercise bike, T = treadmill and W = walk.

Thanks for your help.

Brian W

Try doing it the other way round, like this: A B C D 1) Bike Treadmil Walk 2) Totals > 8.6 17.4 7.4 3) 11-Sep-20 4.5 1.2 4) 12-Sep-20 10 5) 13-Sep-20 6.2 6) 14-Sep-20 6.2 7) 15-Sep-20 4.1 1.2 8) 16-Sep-20 9) 17-Sep-20 10) 18-Sep-20 11) 19-Sep-20

Now in cells B2, C2, D2 just do a SUM() formula.MIKE

You can also Freeze the top two rows so they always show

and just drag the dates down to the end of year

and SUM() your range from your start date to EOY.MIKE

Mmcconaghy, Thank you for that comment. I thought about that approach when I was developing the spreadsheet. I rejected it because I thought that 368 rows would be too cumbersome to use.

Thanks again,

Brian W

I thought that 368 rows would be too cumbersome to use.No more cumbersome then 368 columns, no?

MIKE

If you really need to use Columns then something like this: A B C D E F G H 1) Totals 11-Sep-20 12-Sep-20 13-Sep-20 14-Sep-20 15-Sep-20 16-Sep-20 2) Bike 8.6 4.5 4.1 3) Treadmill 17.4 1.2 10 6.2 4) Walk 7.4 6.2 1.2Again, freeze the first two Columns and they are always in view.

MIKE

In addition to freezing the first 2 columns, reverse the dates. When you are ready to add more data, insert a column to the right of the total column and enter the most recent data there. No need to scroll to the end, just keep pushing the old data to the right.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

mmcconaghy and DerbyDad03, Thank you both for your assistance. I am going to expand the number of rows from n = 31 to n = 62 by doubling up each date e.g., 1, 1, 2, 2, as the first column lists the months.

Thanks again,

Brian W

Sometimes making the data entry just a little more difficult is actually the easiest solution in the long run. The more complex we make a formula to work with a "simplified" data entry process, the better the chance that we will run into problems in the future.

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Are you looking forward to the Xbox Series X?

Discuss in The Lounge

Poll History