MS Excel / VBA help thread

the annual spend should be £1000.

For every company?

I think you're going to need to share actual (fake data) sheet to show where you've got to.

Edit: also, your worked example gives 4 outcomes, not the three you'd expect (red/amber/green)

I don't do budgets, who knew, but I'm a bit confused by what you're after here as it's not all in one place.

You have a total amount to be spent.
You have a cumulative amount spent so far.
You want to know when that cumulative is within 25%,50%, and 75% of the total?

If you have the annual budget in
E6

the weekly spend (which I'm hoping is fed into this sheet from another place and not manually entered) in
F6

then do the percentage of f6/e6

then in h6 write this
=IF(G6<=0.25,1,IF(AND(G6 >=0.25, G6<=0.49),2,IF(AND(G6 >=0.49, G6<=0.74),3,IF(G6>=0.75,4,0))))

then if you apply conditional formatting to h6, you can apply icon sets with a four icon set so that
4 red flag
3 amber flag
2 green flag
<2 no icon

then hide the number when you edit the conditional rule. and you're jsut left with an icon next to the number.

What I hope you have is a table of data, because that's the best way to work in excel for stuff like this.
then you have a visualisation sheet with just the names of your accounts, their budget, their weekly spend (all dragged in by xlookup for values and unique names from the account name), and then this quick icon set.

obviously those of you with more attention will notice "what the fuck are you doing with <=0.25 followed by >=0.25??

=IF(G6<0.25,1,IF(AND(G6 >0.25, G6<=0.49),2,IF(AND(G6 >=0.49, G6<=0.74),3,IF(G6>=0.75,4,0))))

You could make that a lot neater with an IFS
=IFS(G6<0.25,1,G6<0.5,2,G6<0.75,3,G6<=1,4)

I AM SHOWING MY AGE.

IFS are much neater.

This is why I love the hive mind

I appreciate hive pints.

What G in this? I get what e and f are

in g6 you divide f6 by e6

So you spend a fair bit of time working out how to do some lovely traffic light visualisation using conditional formatting in excel. I don't know either.

But now it comes to MOVING IT INTO POWERPOINT.

how do you keep it looking nice, how do you set the size and limits and all that?

Things may have moved on but I used to do this full time... if you are asking what I think you are asking, the main procedure was something like:

1. Find out the sizes in cm of your content areas in PPT.

2. Draw a rectangle/set of rectangles in Excel that correspond to these sizes. Set their properties so they don't move or size with cells, and colour bright pink outline, or something for visibility, no fill

3. Wrangle your Excel content so it fits inside the rectangle. (For a chart you can just directly set size properties, though)

4. Copy Excel content, paste as picture at 100% in PPT. Sometimes you might need to prepare larger in Excel and paste at smaller size, if you do this it's a lot easier to stick to simple size ratios.

5. If you need to stick to colour palette, fonts etc, get it right in XLS first

6. Outsource to a lower cost country

I can probably give more advice if there's a specific issue.

yeah that sounds about right
thanks!

6 is winning

Excel and python eh?

Snakes and ladders

How do you add a data range. From say £0 to a million with £10k increments in between?

=SEQUENCE(101,1,0,10000)

Put that into excel it’s done nuttin

What version of Excel are you on? The neat solutions generally require dynamic arrays which are the more modern versions of Excel.

Otherwise I'd probably just go with

=A1+10000

in A2 and copy that down until you hit 1m

And what if I wanted to add in a range.

0-10000
10001-20000
Etc etc

What do you want to do?

I want a range

1-2
2-3
3-4

=SEQUENCE(100,1,0,10000)&" - "&SEQUENCE(100,1,10000,10000)

will do it if you have a reasonably recent version of excel.

=(ROW()-1)*10000&" - "&(ROW()*10000)

copied down for an old version of Excel (you may need to adjust what you are subtracting depending on which row you are starting at).

Yes, but I'm going to guess there's a reason you want to create these bins. Are you doing some sort of grouping for charting?
https://www.statology.org/data-binning-in-excel/