How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Bryan Your Own Question

Bryan
Bryan, Programming Tutor
Category: Programming
Satisfied Customers: 1445
Experience:  Delphi, c, c++, c# and even a bit of Assembler thrown in. I've been programming for over 25 years. What do you need to know
59162784
Type Your Programming Question Here...
Bryan is online now
A new question is answered every 9 seconds

Am I able to send you the spreadsheet I am working on?

Customer Question

Am I able to send you the spreadsheet I am working on?
Submitted: 6 months ago.
Category: Programming
Customer: replied 6 months ago.
that you can help me write a formula or help me determine if a macro is necessary?
Expert:  Bryan replied 6 months ago.

Sure. Just attach it using the paperclip icon. Then you can explain what it is that you are trying to do.

Customer: replied 6 months ago.
sorry for the delay
Expert:  Bryan replied 6 months ago.

No problem. Go ahead and explain what you are trying to do while I take a look.

Customer: replied 6 months ago.
ok. column L has formulas written. because there are spaces, I can't sort properly or at all. I need to find all the results (yes, no, and #sum) and list the column A, H, I, J next to those results in ascending order on a separate tab and be able to sort. Or if there is an easier way, I am open.
Expert:  Bryan replied 6 months ago.

Alright. Give me a minute.

Customer: replied 6 months ago.
maybe a Vlookup?
Customer: replied 6 months ago.
I can't give up the spaces between players because of the formulas in column I. We will be adding scores as the season goes on.
Expert:  Bryan replied 6 months ago.

Ok, well first we need to fix the errors on I and J, then we can fix L. How quickly do you need this done?

Customer: replied 6 months ago.
what errors are you referring to? the formulas in H require 3 scores, and if there are not 3 scores, I am ok with the result "#sum"
time frame- tomorrow or Thursday will be fine. What am I dealing with here-- formulas or macros? Thanks- DA
Customer: replied 6 months ago.
Sorry--- the formulas in I require 3 scores, Not H
Expert:  Bryan replied 6 months ago.

This can all be done with Formulas. I am getting #NUM calculation errors that I need to fix before I can work on the L calculation. Which version of Excel are you using? Or are you using a different spreadsheet app than Excel? I can get to work on this and should have it for you sometime within the next 12-18 hours.

Customer: replied 6 months ago.
I am unsure of the excel version I am using. probably 2000.
If any player does not have three scores, I would prefer a result of "not enough scores" or "#sum" is fine too.
If you think my formulas throughout the sheet are not adequate I am open to that.
This sheet attempts to calculate player's best three scores (and differential as shown in the formula), and which player's best three as compared to their GHIN handicap which is a manual entry, not a formula. Thanks-
Expert:  Bryan replied 6 months ago.

Ok. That could be why I am getting an invalid number calculation on those columns. I will need to install Office 2000 to verify that. Are you using Windows for this? And if so, do you know which version?

Customer: replied 6 months ago.
Microsoft office version 2010 plus.
Operating system is windows but can't figure out which version. I'll keep looking.
Customer: replied 6 months ago.
Just found it. Windows 7 Enterprise.
Expert:  Bryan replied 6 months ago.

Ok. I'm running Office 2013 in Windows 7 Pro. I'll install 2010 to make sure we keep compatibility. I'll have something for you tomorrow (12-18 hours from now).

Expert:  Bryan replied 6 months ago.

Can you send me a sheet with a full set of rows? Or at the very least 9 rows? Does col I look correct in your set of data? How about col J?

Customer: replied 6 months ago.
I will have to send this later.
i am unsure why you need a full sheet with at least 9 columns. Thx. DA.
Expert:  Bryan replied 6 months ago.

My request for the extra data has to do with the Average function used in Col I, which in turn affects Col J. Which is also my reason for asking about those Columns.

Expert:  Bryan replied 6 months ago.

What is Column I supposed to represent? And how does it change based on subsequent rows? I just filled in fake values for G8 (11.6) and G9 (11.7) to get the Small calculations to work (since you are trying to retrieve 3 values, it needs at least 3 values to work with. I could have used any other 2 rows in G, I just chose those two because they were there). The Average of those will always be 11.6 (just because of the fake values I put in). So depending on how the formula changes for subsequent rows, it will either stay 11.6 in col I for all rows, or it will differ if you average other cells.

Then we come to Column J. I'm not sure why you are using =SMALL(I7:I7,1) because it's not an array that is stored in I, just an average of the 3 Small values. You could have just as easily used =I7

However, once I put in the fake data, I got rid of the #NUM error in I and J plus got a "No" in L

So that's why it would be best to have more data and an explanation of what the data should look like in Cols I and J. Once we get those worked out, then we can move on to your original problem.

Customer: replied 5 months ago.
I see. Thx for the elaboration. Entering fake values to eliminate the #SUM results is fine because as I add more scores, I will override your fake entries anyway.
I will look into col J. The formula should have been looking at column H and I, not just I. I will let you know when I can get back to a computer. Might have to be after work later today.
Expert:  Bryan replied 5 months ago.

That's fine. Just let me know when you're ready to continue.

Expert:  Bryan replied 5 months ago.

Did you still want to work on this, or have you found another solution?

Related Programming Questions