Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculate remaining balance

0

Hi

This is almost the same as the previous question. as I copied the formula to a different spreedsheet. but it's not working even thou I think I changed all relivant figures.

The current balance figure in C10 should show the remaining balance but shows blank.

=OFFSET(B16,C9,4)

I changed = COUNTIF(B17:B39,"<"&$C$2) to match the actual number of payment. this works

Sorry

excelfinance
MS Office Version: 2019
posted by: wildecoyote1966 (rep: 30) on Fri Dec 17, 2021 at 6:04 pm
Answer
Discuss

Answers

0
Selected Answer

Albert

If you have in C9:

= COUNTIF(B17:B39,"<"&$C$2)
then the formula in C10 should be:
=OFFSET(B16,C9,3)
i.e. you just got the wrong number of offset columns. 0 means the same column B and the 3 above means three columns to the right so E (where your balance numbers are).

Note that you can have negative offsets for both rows and columns (provided that never produces a number less than 1).

Hope this works for you.

posted by: John_Ru (rep: 6142) on Fri Dec 17, 2021 at 6:50 pm
Discuss

Discussion

Suggest you search for the Microsoft (or other) guidance on Offset (the function) to understand it better.
John_Ru (rep: 6142) Dec 17, '21 at 6:51 pm
Thanks again John.

sounds easy when you explain it like that.
wildecoyote1966 (rep: 30) Dec 17, '21 at 7:03 pm
Sorry I didn't before but good that you get it now.

Don't forget that you if you have an equation in a cell, you can click on the fx function "button" (after cross and tick/check symbols and to the left of the formula bar) to see the parameters of afunction like OFFSET and there's a "Help on this function" hyperlink to where Microsoft give some useful tips. Which function you get help on depends on where you click in a complex formula (if there are several nested functions).
John_Ru (rep: 6142) Dec 17, '21 at 7:09 pm
I wish windows on my pc wouldn't automatically update.
wildecoyote1966 (rep: 30) Dec 18, '21 at 8:59 pm
That's often an annoying surprise, even if you schedule the times it's allowed to, but assume that has no bearing on this question/answer. 
John_Ru (rep: 6142) Dec 19, '21 at 2:40 am
no bearing just saying.
I go get a coffee and it's shutdown and done whatever then I have 10 recovered files
wildecoyote1966 (rep: 30) Dec 19, '21 at 6:52 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login