MS Excel / VBA help thread

Understood, i'd say Macro and a big button as Plan B but that might be tricky?

My colleagues are on a client site and they won't allow us to setup macro enabled workbooks unfortunately- hence the pain.

is it reorganising the data?
So you have it in one table, but they need to see it in a different layout?
then vlookup etc is probably the way.

they won't allow us to setup macro enabled workbooks

Surely it becomes their (the clients) problem then, not yours?

you know when you write a nested if statement in access (Wait come back!).
And you think "yeah, i like that". Then you think, "it'd be nice if this column i've just created in the table using this nested if could be used as a thing to order the data by at the end".
so how do you do that then?
how do i put criteria on iif([thisthing]="x",1,0) AS thatthing

so that
FROM [table]
Where thatthing = 1

?
or is it not possible to use (alias?) in this way?

Any suggestions for a good spreadsheet/macro for benchmarking a machine's Excel performance. Have a suspicion that our document management system is severely impinging on Excel performance but I'd like some hard data and I'm too lazy to set my own up.

Might not be relevant as I can't speak for access and your table but i would achieve that in an oracle sql script with the use of brackets like

select
this,that, the_other,
thatthing
from
(
select a.this,a.that,a.the_other,
( (a.this-a.that) * the_other) as thatthing
from
mydb.tableA a
where
a.timestamp >= sysdate-1
and
a.timestamp <= sysdate
)
where thatthing = x

Thanks.

Or

select thing1,
(case when something1 = something2 then something2
else something3
end) as thing2
where whatever
order by 2

I knocked up a quick benchmarking worksheet/macro here https://1drv.ms/x/s!Atnj-YxkG3VkhMNtDr6FZg6a09d4pg if anyone has a similar need. Runs through multiple iterations of calculations and gives the time. 25 iterations takes about 3 minutes on my machine.

I've used vlookups, match and sumifs as that's the kind of thing I use at work and they are formulas that are slow to calculate.

If only!

This project is so messed up that I can't even find it funny any more-

Extracting data from their software, messing with it in excel to create reports that can then be shared via email in excel files. THEIR SOFTWARE HAS A COMPREHENSIVE (AND PRETTY DECENT) IN APP REPORTING MODULE ALREADY.

Paying the rent and all that...

In other news just plugged the sheets into Power BI and it has a table tool were you select the relevant columns which is just what I was looking for.

I am trying to calculate the amount of time it will take to pay back a debt.
Screen below.

Is it possible to do a formula like that will Sum 2 columns of data then return the text in adjacent cell when the sum value reaches XXX...?

i.e.
I borrowed £2000.
Add up the amount I have repayed, and the repayments scheduled then return the date that the full £2000 is paid back.

That way I can begin to play with the monthly amounts planned and see how it affects debt free date.

1 Attachment

@WjPrince I put your exact table in a sheet starting at A1.

Then this formula goes into column E (this one for row 2 -i.e. the dec 16 row):

=IF(COUNTIF($E$1:E1,"="&"repaid!")>0,"",IF(SUM($C$2:D2)+IF(E1="",0,E1)>2000,"repaid!",""))

Copy it down column E.

PM me if you want me to email the spreadsheet

if amount 1 = column d
if amount 2 =column e

You'll want to sum column d and column e in two separate cells
d34 and e34 for example
and then write
if(d34=e34,"DEBT PAID MOTHER FUCKER", "KEEP WORKING BITCH")

Oh I missed the date sorry.

Bound to be more elegant ways, but I would:

1. add a column to the right which subtracts your payments leaving the cumulative balance.
   
2. add a further column which says IF(VAR>0,1,0) where VAR is your balance cells, i.e. it creates a column of 1s until you clear the balance, then 0s.
   
3. add a row up top and sum the 1s column, i.e. number of months to clear balance. Add this to MONTH(B1)
   
4. Convert back to text using TEXT(DATE(2000,A1,1),"mmm")
   

heh. beaten to it thrice over :)

That is simpler... that didn't occur to me :)

Although it seems I'm the only one that read the brief.
:^]

Can you use an index match to return the date from column b, when repaid = amount owed?
Or something?
You'd want to total d + e and then compare it to the value of the loan (which could be in a different cell like a1)

.

I laugh in the face of Acceptance Criteria :)

Helper column seems the way to go, although I'm sure there should be a way to do it without so that's bugging me a bit.

Assuming your data is A-D, E2 has

=IF(SUM($C$2:D2)>2000,"Paid","")

and copy that down

Then to return the paid date enter

=INDEX(B:B,MATCH("Paid",E:E,0))

wherever you want the date

Bang.