Find a value in a matrix array

AnandK - Feb 11, 2018 at 01:17 PM - Latest reply: ac3mark 9190 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen
- Mar 4, 2018 at 04:10 PM
Hello,
Is there any formula to find a value in a matrix array
A program in gw basic i want to change it into excel.

Suppose
A B C D E F
0.1, .25,. .3, .55,. .7, .8
.0099, .0087, .0076, .0055, .0032, .0012

And i know a value is 0.2605 which falls between B1 AND C1, and i want to calculate between B2 AND C2 and so on accordingly.

If u can help me can u pls mail me.
See more 

14 replies

Reply to this topic
ac3mark 9190 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Updated by ac3mark on 12/02/18 at 05:00 PM
0
Helpful
13
YOU can "SELECT CASE", as in


variable_a = 5

Select Case variable_a

Case 1 To 5
MSGBOX("Between 1 and 5, inclusive")
' The following is the only Case clause that evaluates to True.

Case 6, 7, 8
MSGBOX("Between 6 and 8, inclusive")

End select


It is that simple!


BTW, this is just a table, or a matrix. If it were an array, it would be in the form of:

Value_A(0)=0.1
Value_A(1)=.25

and so on, storing the values in a variable of VALUE_A, at an index (always starts with 0)!!! So, if I wanted to access the value at the 255 index of VALUE-A, I would say:

dim thevalue
thevalue=VALUE_A(254)
msgbox(thevalue)



Have FUN! Let us know if you get stuck!
ac3mark 9190 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Feb 28, 2018 at 06:39 PM
I already gave you the VBA code to select a range....with SELECT CASE.

Go here and read. If you have questions about ANY of the methods, let us know!
https://support.office.com/en-us/article/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
Thank you so much for trying to help me but i am still not able to understand how to use the select case.. i tried to search on google but cant find it..

Let me make it more simple suppose i want to search for .0260146 value which lies between 0.022 and 0.033
{0.022,0.017441,0.028745,0.040245,0.052324,0.064104,0.077007,0.087963,0.11377,0.140384,0.164046,0.19153,0.216747}
{0.033, 0.014154, 0.025378, 0.036878, 0.048671, 0.060183, 0.073085, 0.084025, 0.109562, 0.135401, 0.164046, 0.191535, 0.216747}

RUK =.0260146
SN% = 22,
ZN% = 2200,
RUMIN = .0025
ZZ% = ZN% / 100 - 1


By using this
350 IF RUK> = 10 THEN KK1% = SN%: GOTO 420
360 IF RUK <= RUMIN THEN KK1% = 2: GOTO 420
370 FOR KK% = 1 TO SN%
380 GET # 1,100 + KK%

390 RUKX=CVS(A$)
400 IF RUKX>RUK THEN KK1%=KK%:GOTO 420
410 NEXT KK%
420 GET#1,99+KK1%:RUK1=CVS(A$)
430 GET#1,100+KK1%:RUK2=CVS(A$)


520 REM ************* reading the given values ******************************
530 FOR I%=200 TO ZN% STEP 100
540 J%=I%/100-1
550 GET#1,I%+KK1%:ALK2=CVS(A$)
560 GET#1,I%+KK1%-1:ALK1=CVS(A$)

590 REM ********** Averaging between two columns *******************
600 LNE = .5575654
610 K1 = LOG (ALK1): K2 = LOG (ALK2)
620 RK = LOG (RUK): RK1 = LOG (RUK1): RK2 = LOG (RUK2)

650 LALK = LNE * (K2-K1) * ((RK-RK1) / (RK2-RK1)) + LNE * K1
660 ALK = 10 ^ LALK: KA (J%) = ALK * FK

690 NEXT I%

Can u now tell me how is it calculating for the complete column averaging the both.
Please explain..
Thank you in advance
ac3mark 9190 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Mar 1, 2018 at 05:06 PM
Well, the average is a logarithm of the variables, because of the LOG(). take a look here:
https://hwiegman.home.xs4all.nl/gw-man/

600 LNE = .5575654 
610 K1 = LOG (ALK1): K2 = LOG (ALK2) {alk1 = CVS(A$) and ALK2=CVS(A$) }
620 RK = LOG (RUK): RK1 = LOG (RUK1): RK2 = LOG (RUK2)

650 LALK = LNE * (K2-K1) * ((RK-RK1) / (RK2-RK1)) + LNE * K1

Plain language as defined at run time:
650 LALK = .5575654 * (K2-K1) * ((RK-RK1) / (RK2-RK1)) + .5575654 * K1

It is that simple! Clear as mud, isn't it?
Sir, i understood that it is a log value..but from the formula

650 LALK = .5575654 * (K2-K1) * ((RK-RK1) / (RK2-RK1)) + .5575654 * K1

I know RK is log value of RUK(0.0260146)
Then what is ALK1, ALK2 and RK1, RK2 values from the table of array.
ac3mark 9190 Posts Monday June 3, 2013Registration dateModeratorStatus April 23, 2018 Last seen - Mar 4, 2018 at 04:10 PM
Well, this is what I found:
RK1 = LOG (RUK1): RK2 = LOG (RUK2)

RUK1 is an array value of CVS, as are the rest of them! Each array variable represents an index of the array, and is dependent on A$, which doesn't seem to represented in this post.

If you ask any more questions, I am going to tell your teacher he isn't doing a very good job!
Respond to ac3mark