how to change formula

[KrazySmile]

how can i get the remainder of a division on a formula?
(the equivalent to the % operator in C++, and Mod in VBA).

i have this:
=VLOOKUP(number;A1:B50;2;0)

what i want is that, instead of the value of 'number', i want it to search the remainder(rest) of the division of number/100, what in VBA, would b something like this (i gess):

aux = number Mod 100

thanks in advance

Filipe Silva

 

[Tazguy37]

=MOD(numerator,denominator) should do it. Hope that helps!

 

[KrazySmile]

yes it does.. but now i have a little problem :S

=VLOOKUP((MOD(number; 100));C1:D50; 100;2;0) //this dont give the result i want, cause it looks on the range for the exact match;

what i really want it to do, is to check each cell in the range given, and compare the MOD(cell_value; 100), with MOD(number; 100);
and if both r equal, then, give back the number on the next column

i can i change the formula above (or a new one) ?

thanks

 

[Tazguy37]

If you don't want to find an exact match, change this:

=VLOOKUP((MOD(number; 100));C1:D50; 100;2;0)

to this: =VLOOKUP((MOD(number; 100));C1:D50; 100;2;1)

 

[KrazySmile]

i do want an axact match!

but betwen the right values, like i write above!

 

[Aladin Akyurek]

i do want an axact match!

but betwen the right values, like i write above!

=INDEX($B$1:$B$50;MATCH(MOD(number;100),MOD($A$1:$A$50;100);0))

which must be confirmed with control+shift+enter instead of the usual enter.

 

[KrazySmile]

it dosent work

(and what do u mean with the shift control enter)

 

[Aladin Akyurek]

it dosent work

That doesn't tell much. How do you suppose this enables one to diagnose the problem that you have with the formula?

(and what do u mean with the shift control enter)

This remark makes me believe that your attempt to use the formula ended up in #VALUE!. If so, you confirmed the formula with just enter instead of control+shift+enter (That is: Pressing Control and Shift keys at the same time followed by hitting the Enter key). Lookup the topics on the so-called "array formulas" in the Help file.

 

[KrazySmile]

yes i figure it out!

sorry! its working now! thanks again!