How to keep the same format cell in sumproduct formula

[Sergio]

Hello,
Maybe this is the simplest question you will see today, but I cant figure it out

I have data in a sheet and the data has numbers and percentages. I am using this formula to create some tables and graphs.

=SUMPRODUCT((Data!$A$3:$A$57=$Q24)*(Data!$D$2:$CU$2=$O$24)*(Data!$D$1:$CU$1=R23)*(Data!$B$3:$B$57=$Q$23)*(Data!$D$3:$CU$57))

Something I did not notice is that always keep everything in % because the format of the cell where the formula is .. it is in %
how can I tell the cell to keep the same format as it finds it on the data sheet?

for example if the number is in % keep % and if the number is data value keep data value

Thanks
Alex

 

[Joe4]

Welcome to the Board!

I don't think it is possible with formulas alone. Excel formulas can only look at the values in other cells, not their formats. And formulas cannot change the format of the cells that they reside in.
Now, you may be able to use Conditional Formatting if you can make some distinctions on the values (i.e. if the number being returned is less than 1, format as a percentage).
Otherwise, I think what you want would require VBA.

 

[Sergio]

ARRRRGGHHH... i dont want to use a Macro as I have already created all my dashboard with Vlook ups and formulas. I will try to see if I can figure it out with conditional formatting.
I was even thinking to run an extra table just to copy original value, but i dont think it will work.

Thanks for the help

 

[Joe4]

I was even thinking to run an extra table just to copy original value, but i dont think it will work.

If you are copying the value by formula, i.e.

=Sheet1!A1

you are going to run into the same issue, in that formulas cannot return the formats of the cells they are linked to!
So I don't see how that would help you.

I will try to see if I can figure it out with conditional formatting.

The key is to try to identify some way in which you can differentiate the values that are returned.
For example, if you percentages are all between 0% and 100%, percentages are really numbers between 0 and 1.
And if your other numbers (non-percentages) are always greater than 1, then you can identify which format you want by the value coming back.

So, you could make a Conditional Formatting rule that says if the value is between 0 and 1, format as a percentage.

If you need any help with that, just post back and let us know.
You would just need to tell us how we can diffentiate the numbers, so we can identify which ones to apply which format to.

 

[Sergio]

Thank you for the tip.

I used the conditional formatting as you mentioned two rules:

between 0 and 1, format as a percentage.
grater than 1, format as general number

thank you again....

 

[Joe4]

You are welcome!
Glad we were able to find a suitable solution.