Excell color coding denial of calculation

February 26th, 2021

So here it goes, i have a spreadsheet which does the following, takes the date, name, what was repaired on it and price in 2 currencies.
Now i take each of the currency separately and calculate their SUM respectively.
Now sometimes in my line of working things happen that someone will not pay, you know those people who want things done but no money to give away. Now i cant just delete it from the spreadsheet i have to have it in my data. So i was thinking, i am familiar with Excel but i dont know it down to the last command. Is there any way to use IF function, or maybe some other, to do this. If i fill that cell, on the subject which wont be payed, if i fill it with lets say red, then excel will discard that value from calculation, and if it is without any fill it will take it into the calculation.
I am using Excel 2010. I hope you understanded what i am asking. Many thanks and have a good one

Answer #1
It can be done with running a macro but it’s more difficult and less reliable in my opinion. Why don’t you just create an extra column(amount paid) and create an if command and if it’s empty to be value of the amount to be paid cell and if you type the amount again there to be 0. You can swap what is zero based on what you do more often to avoid typing and then change the sum accordingly. Made one for you that does what i described and also calculates the amount if someone pays you part of the money.
If you need any changes better to pm me cause i doubt i will notice this. If you still want to do the color thing which i find unreliable read this.
http://excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html
The excel i made

Answer #2
Ok i have to admit that is a good idea. I thought about adding one more column, but colors would be better. But this is way complicated, plus i have too much to rework. Adding a column is easier faster.
Well until i finish my application
Thanks

 

| Sitemap |