Multiples

creinmi

New Member
Joined
Jun 19, 2006
Messages
16
I hope this is an easy one!

This is what I have:

Column A represents the initial year of an event (a value 1 through 10)

Column B represents the number of years it takes for the event to happen again

So if in a given row Column A has 3 and Column B has 5 - the event happens in years 3, 8, 13, 18, etc.

On another sheet I have a column with a list years 1 through 100. In the column next to this I want to put an equation that will tell me if the year in the adjacent cell is one in which the event happens.

Can anyone help me out with this? Is there a function that returns true if a number is a multiple of another? Or one that returns true if a number is an integer?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
OK, on Sheet1 you have 3 in A1, 5 in B1.
On Sheet2 you have 1 ... 100 in A1:A100.

In B1, enter this formula and fill down:
=IF(MOD(A1,Sheet1!$B$1)=Sheet1!$A$1,TRUE,FALSE)

Denis
 

creinmi

New Member
Joined
Jun 19, 2006
Messages
16
Thats great

Thanks - works great

Now an added degree of difficulty - Column C has an expense amount. Is there a way to do a SUMIF equation so that instead of indicating that the year qualifies, that it sums the expense amounts for the qualifiers?

A B C
1 5 $100
2 2 $60

So on Sheet 2 in year 6, I would want the function to return $160

I hope that makes sense - let me know
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
You could do this:

=SUMIF(B:B,TRUE,C:C)

...which will add all expenses adjacent to a match in column B

Denis
 

creinmi

New Member
Joined
Jun 19, 2006
Messages
16

ADVERTISEMENT

The SUMIF is instead of that column so it would need to be based directly on columns A, B & C
 

creinmi

New Member
Joined
Jun 19, 2006
Messages
16
Just realized that doesnt work if the year on Sheet 2 is divisble by the first year of the occurence

Eg.

A B C
2 4 $100

Year 6 returns 0 because there is no remainder

I am stumped!
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
I'm a bit confused about what you need. Could you post a more detailed example, with an expected outcome?
Otherwise we could end up going back and forth quite a lot.
Use Colo's HTMLMaker or erik.van.geit's Table-It to post the data.

Denis
 

Forum statistics

Threads
1,136,266
Messages
5,674,727
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top