• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hello, i need some simple help and im a complete novice.

Q

qt3dot14

New Member
First i want to say that I'm not a spreadsheet expert and don't intend on becoming one. After reading some of these threads I realize that you guys (and gals) are so far above where I ever need to be. Some of the things you guys are doing here are simply amazing.

Next I want to say that I'm using OpenOffice Calc and not Excel but I'm certain that wont be an issue.

This stuff is so complex that I don't even know where to begin any research on my issues so I'm just going to ask the question.

I've created a simple spreadsheet. It's designed to calculate my clients estimated total health insurance premiums paid from now up until retirement age.

So column K Cell 1 starts with the current premium. In this example I'll use $100.

The formula in Cell 2 Column K is =K1*1.2 for an answer of $120.00. Cell 3 Column K is =K2*1.2 for an answer of $144.00 and so on. I go up 20 years, so up to cell 20.

Columns A - F contain various other related information. When I send my analysis report to the client I simply cut and paste columns A - F into a text document and then convert to pdf.

So this is my challenge: Cell F5 contains the number of years until the client's retirement. Lets use 10 years as an example. So when the number 10 is entered into Cell F5 I need a formula to tell the spreadsheet to go over the the K Column and grab the corresponding information from Cell K10 and display that number into Cell F25, which is labeled "Total estimated health insurance premiums paid until retirement". If the number in F5 is 5 I need the spreadsheet to go grab then number in K5 and display that number in Cell F25.

Judging from the other threads here my issue is probably a simple one for someone here but it is beyond my immediate comprehension.

I've looked into the "lookup" and "vlookup" syntax but couldn't make sense of if that's what I need to use. I also tried the "if" syntax but I didn't see a "then else" component so I'm not sure if that can help me either.

If someone could at least direct me to a starting point I may be able to figure it out myself but I have absolutely no idea where to start.

Any help would be appreciated.

Thanx.
 
One small note, looking at Faseeh's formula (which I think is the best answer) and your setup. In your example, you said a 10 year retirement would look in cell K10. But, K1 is current premium, which I think would be counted as year 0. If this is correct, then the 10 year retirement would want to look in cell K11. That would then make the two formulas suggested above be:
=INDEX(K:K,F5+1)
and
=100*1.2^F5
 
Somendra Misra said:
Hi qt3dot14,

Welcome to the forum
In Cell F25, use below formula
=INDEX(K1:K20,F5)

Regards,
Click to expand...


Somendra Misra said:
Hi qt3dot14,

Welcome to the forum.

In Cell F25, use below formula
=INDEX(K1:K20,F5)

Regards,
Click to expand...

First, I want to thank you for welcoming me to the forum. I appreciate that.

When I try your formula it returns Err:508 which is some type of missing bracket.

I went to the OO help and checked the syntax and your formula looks correct to me.

Any ideas?
 
Hi ,

When I check on the Internet , the separator in Calc is the semi-colon ; have you tried :

=INDEX(K1:K20;F5)

Narayan
 
qt3dot14 said:
First i want to say that I'm not a spreadsheet expert and don't intend on becoming one. After reading some of these threads I realize that you guys (and gals) are so far above where I ever need to be. Some of the things you guys are doing here are simply amazing.

Next I want to say that I'm using OpenOffice Calc and not Excel but I'm certain that wont be an issue.

This stuff is so complex that I don't even know where to begin any research on my issues so I'm just going to ask the question.

I've created a simple spreadsheet. It's designed to calculate my clients estimated total health insurance premiums paid from now up until retirement age.

So column K Cell 1 starts with the current premium. In this example I'll use $100.

The formula in Cell 2 Column K is =K1*1.2 for an answer of $120.00. Cell 3 Column K is =K2*1.2 for an answer of $144.00 and so on. I go up 20 years, so up to cell 20.

Columns A - F contain various other related information. When I send my analysis report to the client I simply cut and paste columns A - F into a text document and then convert to pdf.

So this is my challenge: Cell F5 contains the number of years until the client's retirement. Lets use 10 years as an example. So when the number 10 is entered into Cell F5 I need a formula to tell the spreadsheet to go over the the K Column and grab the corresponding information from Cell K10 and display that number into Cell F25, which is labeled "Total estimated health insurance premiums paid until retirement". If the number in F5 is 5 I need the spreadsheet to go grab then number in K5 and display that number in Cell F25.

Judging from the other threads here my issue is probably a simple one for someone here but it is beyond my immediate comprehension.

I've looked into the "lookup" and "vlookup" syntax but couldn't make sense of if that's what I need to use. I also tried the "if" syntax but I didn't see a "then else" component so I'm not sure if that can help me either.

If someone could at least direct me to a starting point I may be able to figure it out myself but I have absolutely no idea where to start.

Any help would be appreciated.

Thanx.
Click to expand...
 
qt3dot14,
I'm not sure why you just quoted your entire original post. You've provided no additional input. We've provided 3 different formulas you could use, w/o much feedback from you on why they don't work.
 
I want to thank everyone for their help so far.

Faseeh, I would like to get some more info from you about your formula. It's not exactly what i need as it stands, but that's because I didn't give complete information in this post as I was unaware that the type of formula you showed me was available as an option. I'll contact you about this at a later date if that's ok with you. You're definitely on the right track but the actual formula I need is a little more complex in the end.

Somendra: I put your syntax in my existing spreadsheet and it always returns an error. But I looked up your suggestion in the OO help files and the syntax is correct.

So I tried your suggestion in a clean spreadsheet with limited cells and it works perfectly. I can copy and paste it into the original spreadsheet and it still works perfectly. But I can't get the formula to work with the existing cells in the existing spreadsheet. I'm baffled.

I've uploaded a pdf with screen shots so you can see my results. Please take a look and see if you can figure out what I'm missing because this should work as is. Obviously I have some detail wrong in my original spreadsheet but I'm just not finding my error.

I also uploaded the spreadsheet in xls format. But keep in mind that it was converted from the ODS format so there could be transition variables in what you see. I would have liked to upload the original ODS spreadsheet but the Chandoo site won't accept that format for upload. Understandable as this is an Excel forum.


Thanx again for everyone in this forum being so helpful.
 

Attachments

Luke M said:
qt3dot14,
I'm not sure why you just quoted your entire original post. You've provided no additional input. We've provided 3 different formulas you could use, w/o much feedback from you on why they don't work.
Click to expand...


Sorry about that. I just hit the reply button before I entered the new information.
 
@qt3dot14

In your first illustration of error, you are passing an array of 3 row in INDEX function F8:F10, but you are asking INDEX to return 8th row which is not there, thats the reason for your error.

Regards,
 
Your "Perseverance" quote is certainly apropos to this type of mental activity!

I have my initial issue corrected. Thanx for your help on all of that. My job is much easier at the moment.

I am uploading another file for you to look at if you have the time. I will be creating one or two more spreadsheets to help me at work and I foresee another challenge on the horizon. It's sort of related to the issues here but maybe a little more complex, at least for me. So I want to get started on that before it has an urgency to it.

Sometimes you just need an expert to assist you. This last issue that you helped me with had some urgency to it, so I really am grateful for your help, and for all the assistance and suggestions from everyone.

Sometimes I need to learn and sometimes I just want to learn! This is a little of both.
 

Attachments

@qt3dot14

Glad you solve it & thanks for your kind words.

Regarding your new issue give me some time to come up with a solution.

But you may not have to wait long as there are more expert people (much much more than myself) here at Chandoo family to help people.

Regards,
 
Dear qt3dot14,

What i have understood from your description is that the table that contains years i) may start from year other then year 1, ii) may start from row other then row 01. Now you want a formula that can tackle these two issues. Is that correct. If yes, you can refer to this sheet:




 

Attachments

Hey Faseeh.

I just saw your sample sheet. I'll be studying that formula. This stuff being out of my expertise I'm sure it will take me a while to decipher the formula and understand it. But thank you for getting me started. I'll be back to let you know my progress.

I'm also posting a pdf of two other formulas I need help with. They are "if, then, else" formulas. They're probably not very difficult formulas for someone like you who's an expert at excel. But for me they're a little out of my immediate reach. The screenshots are on page one and the notations are on page 2. Please take a look if you have a chance.

Thanks a lot for your time.

Vincent
 

Attachments

@qt3dot14
Hi,

Regarding you PDF file problem in Comment #16, please use below formula and see if these are giving you correct result.

In C4 use =IF(B30>2499,B30*20%,B30)
In E26 use =IF(B30<C26,B30,C26+((B30-C26)*0.2))

Just advise if any issue.

Regards,
 
@Faseeh
Sorry my mistake, you must be using this formula in cell other than E26,
PDF File says E26 should equal $8,000 on Pg 2,
So I though OP wants formula in E26.

Regards,
 
Thanx for all the help so far from both of you guy!

There's just one error message I can't figure out. I'll get to that in a minute.

First, just a couple of notes on the comments/formulas above:

1. I'm in OpenOffice and not Excel so the separator is ";" and not ",". This is just for your information as I'm making that change in my spreadsheet when I'm entering your formula suggestions.

2.Faseeh, this formula, =IF(B30>C26,E26=B30,C6+(B30-C26)*0.2), is correct except for C6 is C26. That's just a typo and I fixed that on my end. Also you had E26=B30 highlighted in red and asked if that portion of the formula is correct, and it is correct.

In reference to the formula in E3: =IF(B30>2499;B30*0.2;B30), this formula seems to be working correctly.

In cell B30, if I enter $1,000 cell E4 returns a value of $1,000 which is correct.
" if I enter $2,499 cell E4 returns a value of $2,499 which is correct.
" if I enter $10,000 cell E4 returns a value of $2,000 which is correct.


But it's the value return for the formula in cell E26 that is partially problematic.

=IF(B30<C26;E26=B30;C26+(B30-C26)*0.2)

In cell B30 if I enter $5,000 cell E26 returns a value of $5,000 which is correct.
" if I enter $10,000 cell E26 returns a value of $6,000 which is correct.

But here's the problem:

In cell B30 if I enter $4,000 cell E26 should return a value of $4,000 but instead I get Err:522.

SOLVED:

Formula should be: =IF(B30<C26;B30;C26+(B30-C26)*0.2).

The E= was causing a circular reference error. I just took that part out and the formula works perfectly.

Keep in mind that I'm working in OpenOffice Calc so the formula presented by you guys could very well work as is in Excel.

Thanx again for all the help and inspiration. I'm sure I'll be back here soon!
 
Yes I did. Check the reply just above. I had to make on adjustment as noted in the above reply. Other than that they are working perfectly. I really appreciate your help!
 
You must log in or register to reply here.
Back
Top