Solved How to add a variable to an array formula

Dell / Inspiron 17r
September 8, 2020 at 08:48:05
Specs: windows 10, 2.3/8
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


See More: How to add a variable to an array formula


✔ Best Answer
September 14, 2020 at 08:57:29
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



#1
September 8, 2020 at 11:37:05
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.2

I could go on for hours.

Tell us exactly 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 us every way that "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


Reply ↓  Report •

#2
September 8, 2020 at 13:43:43
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


Reply ↓  Report •

#3
September 8, 2020 at 17:23:57
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.2

Once 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


Reply ↓  Report •

Related Solutions

#4
September 8, 2020 at 18:04:27
">">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


Reply ↓  Report •

#5
September 8, 2020 at 18:48:39
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


Reply ↓  Report •

#6
September 8, 2020 at 19:01:02
DerbyDad03,

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

Thanks again,
Brian W


Reply ↓  Report •

#7
September 8, 2020 at 19:16:11
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


Reply ↓  Report •

#8
September 9, 2020 at 04:31:08
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


Reply ↓  Report •

#9
September 9, 2020 at 04:55:18

Reply ↓  Report •

#10
September 9, 2020 at 05:13:41
DerbyDad03,

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

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

Thank you.
Brian W


Reply ↓  Report •

#11
September 9, 2020 at 06:08:48
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


Reply ↓  Report •

#12
September 9, 2020 at 06:19:10
DerbyDad03,

Sorry about that. I should be. B:4.5/T:1.2


Thank you,
Brian W


Reply ↓  Report •

#13
September 9, 2020 at 09:40:45
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

http://www.skeptic.com/


Reply ↓  Report •

#14
September 9, 2020 at 14:25:33
mmcconaghy,

Thank you for that information.

Brian W


Reply ↓  Report •

#15
September 9, 2020 at 14:48:21
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


Reply ↓  Report •

#16
September 9, 2020 at 15:11:26
DerbyDad03,

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

Thank you.
Brian W


Reply ↓  Report •

#17
September 12, 2020 at 13:59:59
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 the Text To Columns function if you can and simply insert a temporary column to split apart the cells with a slash and use DerbyDad03's original formula.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#18
September 12, 2020 at 15:40:11
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


Reply ↓  Report •

#19
September 12, 2020 at 16:09:12
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

http://www.skeptic.com/


Reply ↓  Report •

#20
September 12, 2020 at 16:17:30
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


Reply ↓  Report •

#21
September 12, 2020 at 17:37:52
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

http://www.skeptic.com/


Reply ↓  Report •

#22
September 12, 2020 at 17:43:16
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

http://www.skeptic.com/


Reply ↓  Report •

#23
September 12, 2020 at 17:44:23
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


Reply ↓  Report •

#24
September 12, 2020 at 18:00:17
I haven't come up with anything either.

message edited by DerbyDad03


Reply ↓  Report •

#25
September 12, 2020 at 18:01:23
I thought that 368 rows would be too cumbersome to use.

No more cumbersome then 368 columns, no?

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#26
September 12, 2020 at 18:23:57
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.2

Again, freeze the first two Columns and they are always in view.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#27
September 13, 2020 at 11:16:33
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


Reply ↓  Report •

#28
September 14, 2020 at 07:34:39
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


Reply ↓  Report •

#29
September 14, 2020 at 08:57:29
✔ 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


Reply ↓  Report •

Ask Question