How do you use excel to calculate multiples of a number? I am creating a
multiplication table for my dtr. up to 12. Can I create the table in Excel
and overlay a function that would calculate the multiples for each number in
the table? Example: "24" (multiples a 24x1; 12x2; 6x4 and 4x3)

T. Scott

One play ..

Enter the numbers in A2 down, eg: 24, 20, 25, ...

Then put in B2:
=IF($A2="","",IF(MOD($A2,COLUMN(A1))=0,$A2/COLUMN(A1)&" x "&COLUMN(A1),""))

Copy B2 across by say 20 cols to U2,
then fill down as far as required to populate the table
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Excel Novice" wrote:
How do you use excel to calculate multiples of a number? I am creating a
multiplication table for my dtr. up to 12. Can I create the table in Excel
and overlay a function that would calculate the multiples for each number in
the table? Example: "24" (multiples a 24x1; 12x2; 6x4 and 4x3)

T. Scott

Here's one way. Certainly not the most clever one, but it demonstrates what's going on and can easily be extended beyond 12.

In B1, enter 1. In C2, enter =B1+1. Copy to the right, ending in M1.
In A2, enter 1. In A3, enter =A2+1. Copy down, ending in A13.
In B2, enter =$A2*B$1
Copy down, ending in B13
Copy B2:B13 to the right, ending in column M

In B15, enter =(MATCH($A$16,B2:B13,0))
Copy to the right, ending in M15. You'll see a few #NAs, don't bother.
IN B16, enter =IF(ISNA(B15),"",IF(B15B$1,"",B15&" * "&B$1))
Copy to the right, ending in M16.

Enter the number you want to investigate in A16. Row 16 will show you the multiples

You can hide row 15 if you dislike it, or you can skip this intermediate step. That requires longer formulas.
Post again in this thread if you can't get it done.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"Excel Novice" wrote in message ...
| How do you use excel to calculate multiples of a number? I am creating a
| multiplication table for my dtr. up to 12. Can I create the table in Excel
| and overlay a function that would calculate the multiples for each number in
| the table? Example: "24" (multiples a 24x1; 12x2; 6x4 and 4x3)
|
| T. Scott

I tried the formula you provided, and verified I had entered it correctly.
When I enter the number I want to investigate in the final step (number
entered in a16), it only works with the #1 (the row shows "1x1"), but not for
other numbers.

In the first line of your instruction below, you say "copy to the right,
ending in M1." I'm assuming you intended to say "M2" (the copy wont go to M1)

T.S.

"Niek Otten" wrote:

Here's one way. Certainly not the most clever one, but it demonstrates what's going on and can easily be extended beyond 12.

In B1, enter 1. In C2, enter =B1+1. Copy to the right, ending in M1.
In A2, enter 1. In A3, enter =A2+1. Copy down, ending in A13.
In B2, enter =$A2*B$1
Copy down, ending in B13
Copy B2:B13 to the right, ending in column M

In B15, enter =(MATCH($A$16,B2:B13,0))
Copy to the right, ending in M15. You'll see a few #NAs, don't bother.
IN B16, enter =IF(ISNA(B15),"",IF(B15B$1,"",B15&" * "&B$1))
Copy to the right, ending in M16.

Enter the number you want to investigate in A16. Row 16 will show you the multiples

You can hide row 15 if you dislike it, or you can skip this intermediate step. That requires longer formulas.
Post again in this thread if you can't get it done.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"Excel Novice" wrote in message ...
| How do you use excel to calculate multiples of a number? I am creating a
| multiplication table for my dtr. up to 12. Can I create the table in Excel
| and overlay a function that would calculate the multiples for each number in
| the table? Example: "24" (multiples a 24x1; 12x2; 6x4 and 4x3)
|
| T. Scott

That first line should have read:

In B1, enter 1. In C1, enter =B1+1. Copy to the right, ending in M1.

Sorry!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Excel Novice" wrote in message ...
|I tried the formula you provided, and verified I had entered it correctly.
| When I enter the number I want to investigate in the final step (number
| entered in a16), it only works with the #1 (the row shows "1x1"), but not for
| other numbers.
|
| In the first line of your instruction below, you say "copy to the right,
| ending in M1." I'm assuming you intended to say "M2" (the copy wont go to M1)
|
| T.S.
|
| "Niek Otten" wrote:
|
| Here's one way. Certainly not the most clever one, but it demonstrates what's going on and can easily be extended beyond 12.
|
| In B1, enter 1. In C2, enter =B1+1. Copy to the right, ending in M1.
| In A2, enter 1. In A3, enter =A2+1. Copy down, ending in A13.
| In B2, enter =$A2*B$1
| Copy down, ending in B13
| Copy B2:B13 to the right, ending in column M
|
| In B15, enter =(MATCH($A$16,B2:B13,0))
| Copy to the right, ending in M15. You'll see a few #NAs, don't bother.
| IN B16, enter =IF(ISNA(B15),"",IF(B15B$1,"",B15&" * "&B$1))
| Copy to the right, ending in M16.
|
| Enter the number you want to investigate in A16. Row 16 will show you the multiples
|
| You can hide row 15 if you dislike it, or you can skip this intermediate step. That requires longer formulas.
| Post again in this thread if you can't get it done.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|
| "Excel Novice" wrote in message
...
| | How do you use excel to calculate multiples of a number? I am creating a
| | multiplication table for my dtr. up to 12. Can I create the table in Excel
| | and overlay a function that would calculate the multiples for each number in
| | the table? Example: "24" (multiples a 24x1; 12x2; 6x4 and 4x3)
| |
| | T. Scott
|
|
|

Thank you so much!!!! This is going to help my dtr. tremendously.

T.S.

"Niek Otten" wrote:

That first line should have read:

In B1, enter 1. In C1, enter =B1+1. Copy to the right, ending in M1.

Sorry!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Excel Novice" wrote in message ...
|I tried the formula you provided, and verified I had entered it correctly.
| When I enter the number I want to investigate in the final step (number
| entered in a16), it only works with the #1 (the row shows "1x1"), but not for
| other numbers.
|
| In the first line of your instruction below, you say "copy to the right,
| ending in M1." I'm assuming you intended to say "M2" (the copy wont go to M1)
|
| T.S.
|
| "Niek Otten" wrote:
|
| Here's one way. Certainly not the most clever one, but it demonstrates what's going on and can easily be extended beyond 12.
|
| In B1, enter 1. In C2, enter =B1+1. Copy to the right, ending in M1.
| In A2, enter 1. In A3, enter =A2+1. Copy down, ending in A13.
| In B2, enter =$A2*B$1
| Copy down, ending in B13
| Copy B2:B13 to the right, ending in column M
|
| In B15, enter =(MATCH($A$16,B2:B13,0))
| Copy to the right, ending in M15. You'll see a few #NAs, don't bother.
| IN B16, enter =IF(ISNA(B15),"",IF(B15B$1,"",B15&" * "&B$1))
| Copy to the right, ending in M16.
|
| Enter the number you want to investigate in A16. Row 16 will show you the multiples
|
| You can hide row 15 if you dislike it, or you can skip this intermediate step. That requires longer formulas.
| Post again in this thread if you can't get it done.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|
| "Excel Novice" wrote in message
...
| | How do you use excel to calculate multiples of a number? I am creating a
| | multiplication table for my dtr. up to 12. Can I create the table in Excel
| | and overlay a function that would calculate the multiples for each number in
| | the table? Example: "24" (multiples a 24x1; 12x2; 6x4 and 4x3)
| |
| | T. Scott
|
|
|