Posts by GradB

Quote from cytop

INDIRECT() is the simplest way to do this but it is a volatile function - it'll recalculate every time any other formula is recalculated (Like the NOW() function). It's not usually a problem but in a big workbook a lot of volatile functions could start to be a drain on resources... just something to bear in mind.

Nice to see a self-answered Q

Thank you, noted!

I think I found the solution.

The formula that should be used in conjuction with the SUM is INDIRECT.

I'm attaching the same file, but with the correct formula.

Of course, if there's any other - more elegant way - I'm happy to hear about it!

Thanks.

Hi,

I'd like to create a formula that would be generated based on the input in two other cells.

The input in those two cells would represent the cells (location) where the formula should start and where it should end.

For example, I'd like to create a SUM formula that would take input from such two cells as start and end of calculation.

I'm attaching an example.

Thank you and best regards.

Hi Carim,

thank you, this is exactly what I wanted to achieve. So, it's AND and not IF! See? That's why I come to this forum.

So, what happened? Is it going to happen again? Is there any other place to go in case this website implodes?

Thank you again!

Hi,

first of all, I'm glad this forum is back (or was it just me?) - I could not access the site for about 2 weeks and felt really bad for losing such a great resource.

Anyway, I'm glad this works again.

A question: I have a list of dates which I'd like to highlight with conditional formatting. I'd like to highlight a date that is within two days (plus or minus) from the current/present day.

I tried using "IF" statement in the formula part of Conditional Formatting but somehow I couldn't get it to work.

I'm attaching a file with more explanation. I hope someone like Carim can help again...

Thanks and best regards.

Hi Carim,

thank you for the formula and the file as well!

I'll have a look and see how this RANK and COUNTIF work together. Am a bit slow in the head.

I'm Ok with having extra columns with formulas in the sheet, so no need for invoking Visual Basic.

Thank you!

Hi Carim,

yes, you're right. If they'd be tied, I'd use some other metric to position one or the other ahead, but for this exercise (as I can then pick it up from there myself) I'd just go with the alphabetical names. Since I replaced their names with numbers (like "Mr.3" instead of "John"), let use this rule in case of a tie: the smaller the number, the better the position. So, in case of the example, it would be Mr. 3 before Mr. 10.

I hope it makes sense to you.

Thanks.

Quote from Carim

Hi,

Whenever you are facing tied scores, you do need to establish a specific rule in order to deal with tie breaking ...

Hi Carim,

I have an additional question. How can I also identify another member that has the same (one of Top5) performance spots?

Please, see the attached file. In it, "Mr.3" with 16 consecutive submissions is in the 3rd place, but so should be "Mr. 10". Is there a way to capture that, too?

Thanks.

Thank you very much. I knew it must be the Index formula, it's just I couldn't put it together myself. This way, I can learn for the future.

Much appreciated!

Hi,

I have a list of numbers entered in rows.

Each row denotes one measurement of compliance.

For anonymity, I removed the names of 23 team members in the top row and replaced them with numbers from 1 to 23.

I'm struggling with the following: I'd like to set up a leader board and for that I would need to know (at least for the last row in this list) who the top 5 performers are. Performance is the number in the row, e.g. in the attached example sheet, "Mr.19" is the best performer in this last measurement because he's got the highest score (57), with "Mr.22" being second with 40 consecutive submissions.

Please, can you help me with a formula that would identify the top 5 performers, say, in columns Z to AE. There could be a tie in one of the top 5 places, so I added a column for the 6th placed.

Thank you

Hi Mario,

thank you, however, writing VBA is not one of my biggest superpowers, if you know what I mean.

I would *really* appreciate if you could apply this macro to the very file I attached in the original post. From there, I could learn how you did it and apply it on other graphs as well.

I hope it's not too much to ask?

Thanks

Hi Carim,

thanks! This is *exactly* how I want my chart to look like, yes. It's just quite convoluted to me on the first pass. I'll have to study it to be able to adapt it for my data and format.

But I appreciate this a lot! Thanks!

I found a solution: in my supporting column I put the "NA()" into the formula instead of "" or 0.

If there's an ever more elegant solution, I'm all ears.

Hi,

I have a line graph which is showing two lines, let's call them "Plan" and "Actual".

I want to show the "Actual" in a different colour whenever it exceeds the "Plan". In order to do that, i created a third line which I sort of overlay over the "Actual" whenever that happens.

The problem is that that 3rd line suddenly "drops" to 0 and ruins my graph. My not-so-nice temporary solution is to delete the formulas in those cells of the 3rd line where the line should not be shown. But I don't like that solution as it involves constant manual readjustment. Is there a way to resolve this in a better way?

Please, see the file attached.

HowToDrawADiffColourLineInGraph.xlsx

Thanks!

Thanks!!!

Yes, that's the sort of thing I had in mind. I'll have to study the example to get it for my needs.

Thanks a lot!

Carim,

here's a file on the same topic...

HowToMakeCellsWithTheRightValue.xlsx

I'd like to write "A", "D", ... in the part with the Gantt chart, based on what's put in the columns C to I.

Can you help me with that, please? I used Conditional Formatting but the formula I'm struggling with.

Thanks.

Ok, thanks. I guess it's sort of a "no-go" then. That's also helpful.

On a different yet kind of related topic, is there a possibility to "anchor" a specific object onto a chart so that it moves with a specific value-point in the chart when the chart is resized? For example, say i want to create a circle around a specific value in the chart, and then the chart gets resized - in that case, that specific value would show somewhere slighthly offset from where it used to be; would the circle be able to follow (be anchored to) that value?

Hi Carim,

thank you for sharing your formula(s). I had a look at and it all makes perfect sense (now that I know the answer).

I hope you can help me out in another thread I opened a few days ago (relating to gridlines in charts) - still waiting for someone to shed some light on that.

Best regards!

Hi Alan,

thank you.
Rather than a pivot, I'm looking for a formula that would return the correct column in a cell where I put the sentence "I'd like to know...". The reason for it is because I'm planning to use that value in another formula (i just simplified my spreadsheet to make it more obviuos what I'm trying to achieve).

So, can you help me find a suitable formula for it, please?
Thanks.