Multiples

[creinmi]

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?

 

[SydneyGeek]

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]

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]

You could do this:

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

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

Denis

 

[creinmi]

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

 

[creinmi]

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]

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