MS Excel / VBA help thread

Hmmmmm...
Tried that. Don't seem to be able to get it to work.

This is the sheet
https://docs.google.com/spreadsheets/d/1hEBMEqUhfemQt3wvLO8dfGeUpnJXCALlqy82t5uWSNM/edit?usp=sharing

Your if function has the sum of 2000. you borrowed 20000.

change

=IF(SUM($C$6:D6)>2000,"Paid","")

to

=IF(SUM($C$6:D6)>$A$1,"Paid","")

and then it will look at the value in cell a1.

Also. It looks like you want to know when you'll be paid off.

can you let me edit your sheet?

Works for me https://docs.google.com/spreadsheets/d/1Cul580n8PhoAWfAf_raFPvPKRakHAojUhBaoZCuHSno/edit?usp=sharing

Edit: looks like I missed the extra zero

This is probably what you want

essentially you need to know,
"if i make a payment of this much, how many months will it take to pay off, and what date is that?"

Very nice.

You are a clever bunch.

Thank you

https://docs.google.com/spreadsheets/d/1Pm3Ttnen4ggZ5gv4Bl4XAHs1fknmWVD04GT26hB8laA/edit?usp=sharing

Nice and simple but doesn't allow for irregular sized payments

Also, none of these allow for interest on outstanding balance ;) We can complicate this more!

@christianSpaceman

nice and simple. That's me.

I guess you could muck around with it
https://docs.google.com/spreadsheets/d/1Pm3Ttnen4ggZ5gv4Bl4XAHs1fknmWVD04GT26hB8laA/edit?usp=sharing

In that you add up what's been paid already, subtract that from your outstanding, and then divide that by the rough number you want to pay back a month.

(i'm not busy today)

I'm doing something similar for my mortgage repayments.

Get daily average by dividing the amount i've paid off, by number of days since start of mortgage.

Using that average work out the number of days left to pay off the rest by dividing the remaining balance by the daily average.

Than add that number of days to todays date.

This is possibly the dullest first post ever, but the generalised answer to "if i make a payment of this much, how many months will it take to pay off..." allowing for interest, is given by:

=NPER(rate,pmt,pv)

where:
nper = number of periods
rate = interest rate per period
pmt = regular payment per period
pv = present value (outstanding balance)

Thus for £250,000 outstanding at 4% APR, paid off at £1500 a month

=NPER(0.04/12, 1500, -250000)

Gives you about 244 months, or 20 years 4 months. Note if you double the interest to 8% APR, you'll get an error, as the number of payments is infinite, i.e. the balance cannot be paid off.

So you can put that formula in a column of a sheet which tracks actual outstanding balance based on actual payments.

well shit, that's much easier

That assumes regular payments to establish the final date tough, rather than an array of inputs (regardless of the outstanding balance being adjusted for actual payments)

Yep but the original data has constant payments.

For my own mortgage I only use that method to give a simple reckoning of the effect of various interest rates if I pay the required payments.

The proper model has actual and projected payments plotted per month with interest, then I chart outstanding balance against calendar date. Then I can override future payments (or rates) and see when balance hits zero, how curve changes etc.

I didn't know there was an NPer function. Thanks!

Obvs you'd put cell addresses in the formula to allow you to try different scenarios?

Yeah, I just put the numbers straight in the formula to keep it simple, I find it difficult to follow cell refs in examples.

There are also PV, PMT functions etc to find the other terms.

A quick question. Access.
I'm pretty certain this can be done. But I'm not sure how.
I have two tables, cases and contacts.
I can match cases and contacts because contacts has a field "entered as a contact of case".
I can generate a table showing the case and their contacts.
How can I generate individual tables, so that I can show all the contacts of an individual case?

Make Table query should do it?

Yeas. But how can I loop through?
I have 100 rows of data where contacts are matched to their case. How can I say "for each individual case identifier, generate a table of the matched contacts?"

eg
I have a table where person = a - x
Person a has 7 contacts
Person b has 6 contacts
etc etc

how can i generate tables a-x?

I'm a bit tired atm and i'm not sure I've explained it. At the moment I have a table of 100 rows, what i want to do is split by one column so all unique numbers in column b have their own table.

Depends how many 'Cases' there are, if only a few use [input case] to make each table on case by case basis, else fingers crossed someone with more access-fu arrives to reply soon with a way to automate the split.

VBA loop

Thanks!
Similar to a vba loop to split an excel sheet?

Hi

I’ve been trying to figure this out asking on google but don’t think I’m using the correct terms.

Basically I have a spreadsheet, I need two columns with the team in one and the manager in the next column.

I have used conditional formatting, so I put in 1 and it automatically makes that show as ‘team 1’ when you press enter.

I would like it to automatically add the mangers name in the field next to it when you enter the team number.

there are 16 teams, 16 managers.

Anyone have any ideas on what to do here please? I’m a bit novice on spreadsheets

Vlookup

Put a Vlookup in the managers column

Something like =(vlookup,a1,f:g,2,false) where cols f and g contain team and manager table and a1 has the team.