Noob Excel question

[Mojoed]

I'm making a budget/credit card spreadsheet which is supposed to return comments in plain English. For example:

=IF(F6="","",CONCATENATE("You are accruing $",TRUN(I6,2)," per month in interest charges for ",B6,". Paying the minimum, your new balance would only go down $",TRUNC(E6-I6,2)," to $",TRUNC(D6-(E6-I6),2)))

is supposed to return:

You are accruing $129.31 per month in interest charges for Appliance Outlet. Paying the minimum, your new balance would only go down $8.68 to $6597.36

However, it's returning an error, #NAME?

I've verified all the data in the referenced cells is correct, and the formula looks good to me but it's not working.

Any ideas? Thanks. 🙂

-- Mojoed

 

[Shagga]

Originally posted by: Mojoed
I'm making a budget/credit card spreadsheet which is supposed to return comments in plain English. For example:

=IF(F6="","",CONCATENATE("You are accruing $",TRUN(I6,2)," per month in interest charges for ",B6,". Paying the minimum, your new balance would only go down $",TRUNC(E6-I6,2)," to $",TRUNC(D6-(E6-I6),2)))

is supposed to return:

You are accruing $129.31 per month in interest charges for Appliance Outlet. Paying the minimum, your new balance would only go down $8.68 to $6597.36

However, it's returning an error, #NAME?

I've verified all the data in the referenced cells is correct, and the formula looks good to me but it's not working.

Any ideas? Thanks. 🙂

-- Mojoed

=IF(F6="","",CONCATENATE("You are accruing $",TRUN(I6,2)," per month in interest charges for ",B6,". Paying the minimum, your new balance would only go down $",TRUNC(E6-I6,2)," to $",TRUNC(D6-(E6-I6),2)))

Should this be TRUNC?

 

[Mojoed]

Oops! Yea thanks for pointing that out. I can't believe that slipped under my radar. Works perfectly now. I stared at that formula for a good 15 minutes too. :Q

It was ummm, late and I was tired or something. 🙂

Thanks again!

 

[Shagga]

Hehe, gets ya like that now and then.... 🙂

 

[Yossarian]

I recommend using visual basic for that kind of stuff (comes w/excel). It's much cleaner.

 

[Mojoed]

Looks like I gotta learn VB too. 🙂 Would my above example be significantly cleaner and easier in VB? I work lots with Excel, and unfortunately have many many other kludgy formulas like above. Any tips on how to get started with VB for Excel? Just the basics for Excel would be all I need, nothing fancy.

Thanks. 🙂

 

[NeoV]

I think the easiest thing is to record some macro's, then go to the VB editor and see what the corresponding code looks like...

 

[TSDible]

Originally posted by: NeoV
I think the easiest thing is to record some macro's, then go to the VB editor and see what the corresponding code looks like...

That is how I learned.

Trial by fire...

Keep in mind that whenever you use VBA, users must decrease their security settings for Macros to at least Medium. If this is a spreadsheet that you plan on giving out, you will get some calls saying it doesn't work since that security is set to High by default.